Sort by Date works in Google Sheets, but DataTables plugin sees dates as text. Need no javascript...

Spreadsheet_Sam

This question has been asked before, and the answer was to add javascript code to the program you're building. I'm not building a program, I'm using a program. I know some coding, but no javascript, and I don't want to edit the program, just use it.

Re: https://www.danceorangeville.com/classes

No error, so no debugger code.

Sort by Date works in Google Sheets, but DataTables-based WordPress plugin "Inline Google Spreadsheet Viewer" sees Dates like Dec 12 as text strings. The creator of "Inline...Viewer" announced that after years of providing support, they are not providing any more support.

In Google Sheets, my column with dates formatted like Dec 12 sorts properly. Does Inline…Viewer receive only the displayed data (“Dec 15”), not the date data (44910, or 2022/12/15)?
DataTables.net seems to only address this kind of question by talking javascript. I can do some coding but not javascript. Do I need to learn Javascript just for this problem? Am I right that Inline Google Spreadsheet Viewer does not need me to change its code, I just need to learn how to use what’s already coded?
My temporary (bad) solution is to have two columns, one that people can read (for example “Dec 15”) and one for sorting (for example 2022/12/15). For people on cellphones adding a 7th column makes things far too tight. I need to find how to let people sort on a column that appears like “Dec 15”.

Besides Google, I searched all WordPress support forums + Datatables.net for “sort by day” and “sort by date”. To my surprise, no relevant answer.
One answer (relevant to a different plugin, WP Data Access) was to change WordPress’ settings for how the date is displayed. I found that under Settings > General, but that had no effect.

kthorngren

You can read more about why certain date formats are sorted as text in this blog. It also explains the best practice solution for Datatables versions below 1.12. You have 1.10.x. Its a matter of loading the provided plugin, moment.js and defining the date format you have.

Or you can upgrade to Dataables 1.12 or later to handle this without the need of the plugin. See this blog.

Kevin

Spreadsheet_Sam

Thanks.

I read momentjs.com/docs/#/displaying/, but I can't see how to specify day format. I can't see (a) what I would add to the WordPress shortcode for Inline Google Sheets Viewer, or (b) what I would edit within Inline...Viewer's php or js files.

I downloaded the DateTime plugin (and others) but can't see where to install them within WordPress's Inline Google Sheets Viewer plugin. (I can see the Inline...Viewer plugin's contents via FTP.)

I also downloaded DataTables 1.13.1. Same problem.

I tried the CDN method, but I can't find where I would paste the <link...> or the <script...> within Inline...Viewer's files.

I did replace "1.10.20" with "1.13.1" in 2 places within inline_gdocs_viewer.php, but
not using the same code as provided in the CDN section of the Download page.

https://cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css
became
https://cdn.datatables.net/1.13.1/css/jquery.dataTables.min.css

https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js
became
https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js

It didn't totally break my site, so maybe that worked.
Since it's using 1.13.1, does that mean I don't need to install or configure moment.js?

Now that I have 1.13.1, how do I get the column with dates appearing like "Dec 15" to be processed as dates?

Changing to 1.13.1 made my table's background color become transparent. I have tried at least 12 css hacks. How can I get it to return to a white background?

Thanks

Spreadsheet_Sam

In https://cdn.datatables.net/1.13.1/css/jquery.dataTables.min.css I found
table.dataTable tbody tr{background-color:transparent}
so in WordPress's Customization section, the Additional CSS panel, I added:
table.dataTable tbody tr{background-color:white}
but that had no effect, so I tried
table.igsv-table tbody tr{background-color:white}.
Still no effect.

I also tried
tbody tr {background-color:white}.
tr {background-color:white}.

Spreadsheet_Sam

It's too late to edit the above post - it's not possible now. I got the background-color issue fixed, finally. Hard to say how.

I still haven't figured how to tell datatables (1.13) to interpret Dec 15 for example as a date.

kthorngren

Here is the link to the moment.js formatting docs:
https://momentjs.com/docs/#/displaying/

For the data Dec 12 or Apr 5 is looks like the format would be MMM D.

Kevin

Spreadsheet_Sam

Where do I place this?

DataTable.datetime('D MMM YYYY');
$(document).ready(function () {
$('#example').DataTable();
});

Where do I place moment.js? Just anywhere within the Inline...Viewer plugin folder?
Do I need to call upon it, or just place it there?

Spreadsheet_Sam

For moment.js:

In the " $scripts = array( " section of inline_gdocs_viewer.php, I added
,
'moment' => array(
'src' => 'https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.2/moment.min.js',
'deps' => array( 'moment' )
)

Good?

I could alternatively have saved https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.2/moment.min.js
to my laptop then ftp-uploaded it to the js folder within the Inline...Viewer plugin folder.
and then added
,
'moment' => array(
'src' => plugins_url('moment.min.js'),
'deps' => array( 'moment' )
)

Right? Would it be faster to load that way, coming from the same site?

Thanks

Spreadsheet_Sam

Where do I place this?

DataTable.datetime('D MMM YYYY');
$(document).ready(function () {
$('#example').DataTable();
});

Somewhere in inline_gdocs_viewer.php?

I tried to make sense of the examples. Where do I place this?

$(document).ready(function () {
$('#example').DataTable({
columnDefs: [
{
targets: 4,
render: DataTable.render.datetime('Do MMM YYYY'),
},
],
});
});

Somewhere in inline_gdocs_viewer.php?

(I presume I would need to change '#example' to ... something else.)

kthorngren

Where do I place moment.js?

If you view the source of the link you provided you are loading the datatables.js, etc. Place it in that area.

Where do I place this?
DataTable.datetime('D MMM YYYY');

I'm not sure where in the project you are initializing Datatables but that is where it goes. Make sure to change the format to what I posted above.

Kevin

Spreadsheet_Sam

Corrected code for calling the uploaded moment.js file (adding , __FILE__):

,
'moment' => array(
'src' => plugins_url('moment.min.js', __FILE__),
'deps' => array( 'moment' )
)

I'm not sure about the "deps" part. Does it mean dependencies? Was I right to use "moment" twice in that code?

Spreadsheet_Sam

To use moment.js, this is the code I want to enter somewhere:

moment().format("MMM d"); // "Dec 15"

For another column, I want to use

moment().format("ddd"); // "Sun"

I read
https://momentjs.com/docs/
and
https://momentjs.com/guides/

Where do I enter the code? I'm guessing in the middle of the following, replacing the example.

$(document).ready(function () {
$('#example').DataTable({
columnDefs: [
{
targets: 4,
render: DataTable.render.datetime('Do MMM YYYY'),
},
],
});
});

Where do I enter that code?

In the example, column 5 was the column with dates, so why is the code "targets: 4"?
How do I tell it to apply my chosen format to column (for example) 3?

(I want to make dates like 2022/12/15 display as Dec 15 in one column, and as Sun in another column.)

kthorngren

You don't need to use the moment.js functions. Datatables does it for you. Since your table already has the date format you want you don't need to use render: DataTable.render.datetime('Do MMM YYYY'),. Just use DataTable.datetime('MMM D'); to tell Datatables the date format you have.

moment().format("MMM d"); // "Dec 15"

This format is incorrect. The d represents the day of the week. You need D like I posted above. Please DataTable.datetime('MMM D'); just before your Datatables initialization.

If you want to use the date time renderer you will add the config to your current Datatables initialization.

In the example, column 5 was the column with dates, so why is the code "targets: 4"?

Javascript indexes the first element in the array as 0. So column 1 is referenced with targets: 0, etc.

Kevin

Spreadsheet_Sam

Thanks!
"Place DataTable.datetime('MMM D'); just before your Datatables initialization."

I went to insert it into igsv-datatables.js"

[...]

DataTable.datetime('MMM D')
// Initialize tables.
jQuery(igsv_plugin_vars.datatables_classes).each(function () {
var table = jQuery(this);
var dt_opts = {};
if (jQuery.fn.dataTable.defaults.buttons) { // apply defaults for Buttons
dt_opts.buttons = jQuery.fn.dataTable.defaults.buttons
}

[...]

but what if I have MMM D format in one column and 'ddd' in another column?

---

Are you familiar at all with WordPress' shortcode? The Inline...Viewer uses shortcode. WordPress users click to add block type shortcode, and then enter for example

[gdoc key="https://docs.google.com/spreadsheets/d/1QtUoDMdU2AI7IMPQwoUOpzOnXwjgL5LrTXBa1-5P1lo/edit#gid=287180896" datatables_paging="false" query="select A, B, C, D, E, F, G" csv_headers=1]

Ideally I would be able to specify date formats there, with different formats applying to different columns.

kthorngren

You can use DataTable.datetime('...'); for each format you have. Datatables will scan each column and if all the data in the column matches any of the formats you specify then it can sort that column using that format.

Sorry I don't know anything about Wordpress.

Kevin