Fixing script to input 2021-02-01 instead of 2021-02-32

Question

I'm trying to this code in the Script Editor of Google Sheets to insert today's date along with a consistent piece of text, and it was working fine up until Jan 31, 2021, when it started inserting 2021-02-32, 2021-02-33, etc. instead of 2021-02-01, 2021-02-02, etc. Here's the code I'm running:

function daily() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Social Tracker");
  var date = Utilities.formatDate(new Date(), "GMT", "YYYY-MM-DD");
  var aValues = sh.getRange("A1:A").getValues();
  var aLast = aValues.filter(String).length;

  // Assuming A and B have same last row, no need for B
  // If different, need to calculate separately
  var bValues = sh.getRange("B1:B").getValues();
  var bLast = bValues.filter(String).length;
  
  // If A and B are the same, use setValues
  sh.getRange(aLast + 1, 1, 1, 2).setValues([[date,'handle']]);

  var sheet = SpreadsheetApp.getActiveSheet();

  var followers = sheet.getRange(2,5,sheet.getLastRow()).getValues();
  var nextRowJ = getFirstEmptyRow('J');

  var following = sheet.getRange(2,6,sheet.getLastRow()).getValues();
  var nextRowK = getFirstEmptyRow('K');

  var engagement = sheet.getRange(2,7,sheet.getLastRow()).getValues();
  var nextRowL = getFirstEmptyRow('L');

  var likes = sheet.getRange(2,8,sheet.getLastRow()).getValues();
  var nextRowM = getFirstEmptyRow('M');

  var comments = sheet.getRange(2,9,sheet.getLastRow()).getValues();
  var nextRowN = getFirstEmptyRow('N');

  var posts = sheet.getRange(2,4,sheet.getLastRow()).getValues();
  var nextRowO = getFirstEmptyRow('O');


  
  // Record current balance and timestamp at end of columns B & C
  sheet.getRange(nextRowO, 15, 1, 1).setValues([[posts]]);
  sheet.getRange(nextRowJ, 10, 1, 1).setValues([[followers]]);
  sheet.getRange(nextRowK, 11, 1, 1).setValues([[following]]);
  sheet.getRange(nextRowL, 12, 1, 1).setValues([[engagement]]);
  sheet.getRange(nextRowM, 13, 1, 1).setValues([[likes]]);
  sheet.getRange(nextRowN, 14, 1, 1).setValues([[comments]]);

}

// From https://stackoverflow.com/a/9102463/1677912
function getFirstEmptyRow(columnLetter) {
  columnLetter = columnLetter || 'A';
  var rangeA1 = columnLetter + ':' + columnLetter;
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange(rangeA1);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct][0] != "" ) {
    ct++;
  }

And here's an image of the cells that are being filled in by the script. I'm assuming my issue is with the line: sh.getRange(aLast + 1, 1, 1, 2).setValues([[date,'consistent piece of text']]);

How can I adjust this to make sure it follows the next date in A1, ie. inserts 2021-02-01 instead of 2021-02-32?

Ron M · Accepted Answer · 2021-02-05T20:39:19.270

2

Use:

var date = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");

Reason:

Utilities.formatDate(date, timeZone, format) follows this format guidelines

When you use "DD", you specify the Day in a year. If you want to specify the Day of the month you need to use dd

In addition, when you use "YYYY" you are referring to a Week year. If you want to get the actual year use "yyyy" instead.

edited Feb 05 '21 at 20:39

answered Feb 05 '21 at 20:20

Ron M

5,791
1
4
16

just wanted to add that this is correct but it is stupid and counter intuitive! the average person is going to assume it is DD because YYYY and MM were caps! – J. G. Feb 05 '21 at 20:30
1

@J.G. You should use `yyyy` (lower case) other wise the first/last week of the year you might get the wrong year – Rubén Feb 05 '21 at 20:32
1

@Rubén thanks for pointing that out, I will update the answer to point out the year format as well for reference – Ron M Feb 05 '21 at 20:37

Rubén · Answer 2 · 2021-02-05T21:59:43.037

If you find using Java SE Simple Date Format counterintuitive and you are using Google Apps Script V8 runtime instead of using Utilities.formatdate() you might use to Date.prototype.toLocaleDateString

Instead of "YYYY-MM-DD" use "yyyy-MM-dd"

YYYY stands for 4 digit weak year (the first/last week of the year it might return a number different of what you are expecting.
yyyy stands for 4 digit year
DD stands for day year (32 is February 1st)
MM stands for 2 digit month (02 is February)
mm stats for 2 digit minutes

Fixing script to input 2021-02-01 instead of 2021-02-32

2 Answers2