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?

Answer 1

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.