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.

我只是想建立一种公式,将日期从上表上提高,而另一个单元则依靠日期小组。如果我使用一个公式,用日间为时日子的囚室就没有将其改为天。

任何帮助都将受到高度赞赏。谢谢!

我先尝试使用盘点清单,并做一次旁观或旁听器,但只是把日期列入下面所有囚室的最高牢房。

我早就修改了我的法典,我甚至可以再次这样做。

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());
}

[![此处插入图像描述]]

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.

在这种情况下,以下样本说明如何?

Sample script:

在本稿中,为了降低处理费用,采用了Sheets API。因此,。页: 1 并请打上你的名字。并请确认<代码>a1Notation的单位范围。

为了操作这一文字,请在表格上点击ton子,将<条码><>日的功能分配到您的纽克。因此,当你点击纽顿时,该笔文字就开始使用。

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:

另一种做法是,如果你不能使用幻灯片,你也可以使用以下样本。

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);
    });
}

Sample script:

Note:

References:

友情链接