How to increase a date in Google Sheets script?

Question

I'm new to gsheets script. I'm trying to do this: Get a date at a cell, then change it to the day after, after that return it to another cell.

  var range = sheet.getRange('I1'); 
  var date = range.getValues();
  var datetmp = new Date(date);
  datetmp = datetmp + 1;
  newcell.setValue(datetmp).setHorizontalAlignment("center");

I1 cell have a date is 27/12/2019. The result I get is "Fri Dec 27 2019 00:00:00 GMT+0700 (ICT)1". I have search on internet that script code like js. So I do this datetmp = datetmp + 1; but seem it does not work. Is there a way to do this in GoogleSheets. Thank you.

Please take a look to this https://stackoverflow.com/questions/40893620/google-app-script-adding-one-day-to-a-date Hope it helps! — RPDF, Dec 11 '19 at 16:05
Does this answer your question? [Google App Script Adding one Day to a date](https://stackoverflow.com/questions/40893620/google-app-script-adding-one-day-to-a-date) — Rubén, Dec 11 '19 at 16:18
Thank for helping. But my problem is the result I get is a string. I thought that a date +1 is got the next day. But seem gsheets plus a date with string "1" and return my result. I don't know why :( — duc947, Dec 11 '19 at 16:23
datetmp is a date object rather than a number (although stored as a number) https://www.javascripture.com/Date — a-burge, Dec 11 '19 at 16:30
Ya, and I thought that a date obj +1 will get the next date (search on the Internet). Do I wrong... — duc947, Dec 11 '19 at 16:38
If you use the answer below, you will get the next day. Then format the date: https://www.w3schools.com/js/js_date_formats.asp — Suzette Buxmann, Dec 11 '19 at 19:47

Suzette Buxmann · Accepted Answer · 2019-12-11T17:23:23.740

1

Instead of adding just 1, trying adding another day like this:

var date = new Date();
date.setDate(date.getDate()+1);

edited Dec 11 '19 at 17:23

answered Dec 11 '19 at 17:16

Suzette Buxmann

133
5

score 0 · Answer 2 · edited Jul 24 '20 at 22:42

This is actually something that I've had a lot of trouble with in the past so I played around with this a little.

Here's the code:

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Date Tools")
  .addItem('Generate Spreadsheet', 'generateTheSpreadsheet')
  .addItem('Display Format of Active Range', 'displayFormatOfActiveRange')
  .addItem('Clear Format of Active Range', 'clearFormatOfActiveRange')
  .addItem('Set Format of Active Range', 'setFormatOfActiveRange')
  .addItem('Select Column Skip Header', 'jjeSUS1.selectColumnsSkipHeader')
  .addToUi();
}

function generateTheSpreadsheet() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var fA=[['','','','','']];
  var oA=[['Description','Code','Result as Number','Result as Date','Date().valueOf()']];
  oA.push(['','','#0.##########','M/d/yyyy','#']);
  fA.push(['','','','','']);
  if(sh.getLastRow()>1) {
    sh.getRange(1,1,sh.getLastRow(),fA[0].length).clearContent();
  }
  oA.push(['Today','new Date()',new Date(),new Date(),Number(new Date().valueOf()).toFixed(0)]);
  fA.push(['','','#0.##########','M/d/yyyy','#']);
  oA.push(['Tomorrow at Midnight','new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()+1)',new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()+1),new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()+1),Number(new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()+1)).toFixed(0)]);
  fA.push(['','','#0.##########','M/d/yyyy','#']);
  oA.push(['Yesterday at Midnight','new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()-1)',new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()-1),new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()-1),Number(new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate()-1)).toFixed(0)]);
  fA.push(['','','#0.##########','M/d/yyyy','#']);
  oA.push(['Tomorrow same time setDate()','new Date().setDate(new Date().getDate()+1)',new Date().setDate(new Date().getDate()+1),new Date().setDate(new Date().getDate()+1),Number(new Date().setDate(new Date().getDate()+1).valueOf()).toFixed(0)]);
  fA.push(['','','#0.##########','M/d/yyyy','#']);
  oA.push(['Tomorrow same time','new Date(new Date().setDate(new Date().getDate()+1))',new Date(new Date().setDate(new Date().getDate()+1)),new Date(new Date().setDate(new Date().getDate()+1)),Number(new Date().setDate(new Date().getDate()+1).valueOf()).toFixed(0)]);
  fA.push(['','','#0.##########','M/d/yyyy','#']);
  sh.getRange(1,1,oA.length,oA[0].length).setValues(oA);
  sh.getRange(1,1,fA.length,fA[0].length).setNumberFormats(fA);
  
}

function displayFormatOfActiveRange() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getActiveRange();
  var row=rg.getRow();
  var col=rg.getColumn();
  var fA=rg.getNumberFormats();
  var vA=rg.getValues();
  var html='<style>th,td{border:1px solid black;padding:2px 4px;}</style><table><tr><th>Row</th><th>Column</th><th>Value</th><th>Format</th></tr>';
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {
      html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>',row+i,col+j,vA[i][j],fA[i][j]);
    }
  }
  html+='</table>';
  var userInterface=HtmlService.createHtmlOutput(html).setWidth(600);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Values & Formats');
}

function clearFormatOfActiveRange() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getActiveRange();
  rg.setNumberFormat('');
}

function setFormatOfActiveRange() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getActiveRange();
  var fA=rg.getNumberFormats();
  var resp=SpreadsheetApp.getUi().prompt("Formats", fA[0][0], SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if(resp.getSelectedButton()==SpreadsheetApp.getUi().Button.OK) {
    rg.setNumberFormat(resp.getResponseText());
  }
}

Here's what my spreadsheet looks like:

Spreadsheet Link

Column A is a simple description of each row.

Column B is the actual code that's in Column C and Column D

Column C has the number format of #0.##########

Column D has the number format of M/d/yyyy

Otherwise column C and D are the same.

Column E Javascript Date().valueOf() method result

Column F is a simple cell function that adds 1 to the value that is in column D of each row

Columns G and H are the isDate() funtion on columns C and D. It's interesting to note that the only difference between columns C and D is formatting so I'm guessing that ISDATE() tells you more about format of a cell as opposed to what kind of object is inside of the cell.

Row 4 was one of the other answer for a time and you can see that it returns the number of milliseconds and so the spreadsheet doesn't really seem to know what to do with that. When it adds one to it as it does in Column F it ends simply increasing the number of millisecond by one.

Row 5 uses the same method as in row 4 but I pass it through the Date() constructor one more time and it returns a nice date object which the spreadsheet happily turns into one of it's Date objects and in column C and D it proceeds to display the values properly as a number in one case and a date in the other. Also it seems to work well with other spreadsheet functions as it does in column F by generating the next days date.

How to increase a date in Google Sheets script?

2 Answers2