CODEWITHSUNDEEP
google-apps-script
John Velella
John Velella

Reputation: 31

Moving up dates with app script but it returns different format?

I am running a script that will move up a date in each cell. I want it to move all the selected dates up x days. When I run the function it works but changes the format of the date:

"J" is just simple dates "7/9/2019" And I am trying to run the script (which emails a copy of my invoice to the client, that part works great)

function updatePM(sheet, email){
  var value;
  var datevalue
  var emails = sheet.getRange("G3:G" +  
sheet.getLastRow()).getValues();
  for (var i = 0; i < emails.length; i++)
    if (emails[i][0] == email){
  value = sheet.getRange("L" + (i+3)).getValue() + 1
  datevalue = sheet.getRange("J" + (i+3)).getValue() - 1
  sheet.getRange("L" + (i+3)).setValue(value);
  sheet.getRange("J" + (i+3)).setValue(datevalue);

So I think it is the line "datevalue = sheet.getRange("J" + (i+3)).getValue() - 1" that needs to be changed, I have never moved a date and not sure how to keep the same format.

Upvotes: 1

Views: 47

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15375

Sheets and Apps Script use different bases for time units so you're getting back Unix Time after the script. Try changing:

value = sheet.getRange("L" + (i+3)).getValue() + 1
datevalue = sheet.getRange("J" + (i+3)).getValue() - 1
sheet.getRange("L" + (i+3)).setValue(value);
sheet.getRange("J" + (i+3)).setValue(datevalue);

to:

value = sheet.getRange("L" + (i + 3)).getValue() + 86400000
datevalue = sheet.getRange("J" + (i + 3)).getValue() - 86400000
sheet.getRange("L" + (i + 3)).setValue(new Date(value)).setNumberFormat("MM/dd/yyyy");
sheet.getRange("J" + (i + 3)).setValue(new Date(datevalue)).setNumberFormat("MM/dd/yyyy");

You can see the available Date formats here.

Upvotes: 1

Related Questions