Google Apps Script – Date Manipulation in GAS Script

google sheetsgoogle-apps-script

I am trying to add 7 days to the current date and name a sheet with that value. Below is the code I would think would work, but it keeps the date format and adds the 7 days to the year rather than the day.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var tz = ss.getSpreadsheetTimeZone();
var sheets = ss.getSheets();
var date = Utilities.formatDate(new Date(), tz, 'MM-dd-yy');
var day1 = date +7
sheets[1].setName(day1);  // Rename second

Do I have something incorrect?

Best Answer

Short answer

Use setDate JavaScript method.

Explanation

Google Apps Script use JavaScript to handle date objects, so you should use JavaScript methods instead of typical Google Sheets methods.

By the other hand, Utilities.formatDate() returns a string, not a date object.

The following script set the name of the second sheet as the date of seven days from now:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tz = ss.getSpreadsheetTimeZone();
  var date = new Date();
  var nextDate = new Date();
  var days = 7;
  nextDate.setDate(date.getDate() + days);
  var stringDate = Utilities.formatDate(nextDate, tz, 'MM-dd-yy');
  ss.getSheets()[1].setName(stringDate)
}

References

Add days to JavaScript Date

Automatic insertion: requires Apps Script

Use the following script (Tools > Script Editor):

function onEdit(e) {
  if (e.range.getSheet().getSheetName() == 'Sheet1' && e.range.getColumn() == 4) {
    e.range.offset(0,-1).setValue(e.value.length>0 ? new Date() : '');
  }
}

On every edit, the script checks whether the edit was to "Sheet1" (put the name of the relevant sheet here) and to the column 4 (that is D). If so, it fills the cell directly to the left (offset(0,-1)) with the current date. Unless the edit removed content from column D; then the corresponding C cell becomes empty.

Manual insertion: no script

Make the habit of pressing Ctrl+; in column C when editing column D. This inserts the current date.

Google-sheets – Google Sheets Script Editor action not applying to all sheets

Get the sheet that was edited. Change code to:

var sheet, ss;

ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getActiveSheet();//Get the active sheet

Now the sheet variable is the active sheet that was just edited. So anything done with sheet will affect that sheet.