Make Button to increase all dates in Column by 1 Google Sheets Apps Script

Question

I have a spreadsheet and I need to increase dates in the ranges 'G5:G27','T5:T27','AG5:AG27','AT5:AT27' by one month. I want to create a button to run this script as i will be copying the sheet and then wish to just press the button to update all the dates by 1 month.

I would just create a formula to increase the date from the previous sheet but the sheet another cell relies on the date cells. If I use a formula the cell that reads the date cells doesn't read it as a date.

Any help would be greatly appreciated! Thank you!

I've tried using rangelist and doing either a for while or foreach loop but i only get it to list the date in the top cell for all the cells below.

I've changed my code so much I can't even get that to work again.

Edited: This is what I have so far. I am able to copy the dates over into the next column but I don't know how to add a month to the dates.

function date() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet11');
  var date = new Date();
  var valuestocopy = sheet.getRange(1, 5, 25, 1);
  var copyto = sheet.getRange(1, 6, 25, 1);
 
sheet.getRange(copyto.getA1Notation()).setValue(date).setNumberFormat("M/D/YY");
copyto.setValues(valuestocopy.getValues());
}

[![enter image description here][2]][2]

Answer 1

From the discussion in the comment, I believe your goal is as follows.

• You have a sheet in Google Spreadsheet. The sheet has the date objects in the cells 'G5:G27','T5:T27','AG5:AG27','AT5:AT27'.
• You want to add 1 month to all date objects in the cells.
• You want to run the script by clicking a button on the sheet.

In this case, how about the following sample script?

Sample script:

In this script, in order to reduce the process cost, Sheets API is used. So, please enable Sheets API at Advanced Google services before you run the script. And, please set your sheet name. And, please confirm your cell ranges of a1Notation.

In order to run this script by clicking a button on the sheet, please assign the function date to your button. By this, when you click the button, this script is run.

function date() {
  var sheetName = "Sheet1"; // Please set your sheet name.
  var a1Notations = ['G5:G27', 'T5:T27', 'AG5:AG27', 'AT5:AT27']; // This is from your question.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var ranges = a1Notations.map(e => `'${sheetName}'!${e}`);
  var { valueRanges } = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges, valueRenderOption: "UNFORMATTED_VALUE" });
  var data = valueRanges.map(({ values }, i) => {
    var values = values.map(([v]) => {
      var unixTime = (v - 25569) * 86400 * 1000; // Ref: https://stackoverflow.com/a/6154953
      var temp = new Date(unixTime);
      temp.setMonth(temp.getMonth() + 1);
      var serialNumber = (temp.getTime() / 1000 / 86400) + 25569; // Ref: https://stackoverflow.com/a/6154953
      return [serialNumber];
    });
    return { range: ranges[i], values };
  });
  Sheets.Spreadsheets.Values.batchUpdate({ data, "valueInputOption": "USER_ENTERED" }, ssId);
}

• When this script is run, the cell values (date objects) are retrieved from ['G5:G27', 'T5:T27', 'AG5:AG27', 'AT5:AT27']. And, 1 month is added to the values. And then, the updated values are put into the same range of ['G5:G27', 'T5:T27', 'AG5:AG27', 'AT5:AT27'].

Note:

• As another approach, when you cannot use Sheets API, you can also use the following sample script.

  function date() {
    var sheetName = "Sheet1"; // Please set your sheet name.
    var a1Notations = ['G5:G27', 'T5:T27', 'AG5:AG27', 'AT5:AT27']; // This is from your question.
  
    SpreadsheetApp
      .getActiveSpreadsheet()
      .getSheetByName(sheetName)
      .getRangeList(a1Notations)
      .getRanges()
      .forEach(r => {
        var values = r.getValues().map(([v]) => {
          v.setMonth(v.getMonth() + 1);
          return [v];
        });
        r.setValues(values);
      });
  }
  

References:

• Method: spreadsheets.values.batchGet
• Method: spreadsheets.values.batchUpdate