6 thoughts on “GAS – format cell number format”

1. I am looking for GAS tips for table and having trouble with the date format.
   I tried the above scripts in gs. however, the date cannot be delivered to javascripts or html page.
   So could you please let me know more detail how to use this.

   thank you

   TS/

   Reply ↓

   • Do you want to keep the date as a date when transferring to html/javascript? If so, it might be easier to convert the date number to a text, the use javascript to reformat

     Reply ↓

     • Thank you for the response and sorry if i am bugging you.
       If then, how i can convert the column date to string format and deliver the string date with other data to html/javascript.

       For example, I can only transfer the data to html with the below script and I don’t know how to do if the “H” column is date format.

       data = SpreadsheetApp.openById(ssID).getSheets()[1].getDataRange().getValues();

       Then, in html, callback to draw table.

       Reply ↓

       • Like so:

         function myFunction() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName("Sheet1");
var rng = sh.getDataRange().getValues();
Logger.log(rng);
for (var i=1;i

         before
[[Name, Age, Date], [John, 24.0, Fri Nov 15 00:00:00 GMT+00:00 1996], [Dave, 36.0, Tue Jun 12 00:00:00 GMT+01:00 1984], [Sue, 18.0, Tue Jul 23 00:00:00 GMT+01:00 2002]]
after
[[Name, Age, Date], [John, 24.0, 15/11/1996], [Dave, 36.0, 11/06/1984], [Sue, 18.0, 22/07/2002]]

         Reply ↓

         • So I could use the below to string for raw 7 and one thing please let me know how to return the data for transfer.

           for (var i = 1; i < data.length; i++) {
           var row = data[i];
           var date= row[7];
           var formattedDate = Utilities.formatDate(new Date(date), "GMT", "yyyy-MM-dd");
           Logger.log(formattedDate);
           }

           P.S I used awesome table for sometime and now i am changing all my tables and chart using GAS myself.

           I saw your scripts and really helpful.

           thank you,

         • You just set your reformatted data to a variable, and pass this back to the html, in my example it is still “rng”