0

Currently, I'm using (Month(NOW()) to set the current month number in cell $D$1 of a spreadsheet. I know Month and Now are volatile functions and they're making the over sheet update way too slowly, so what I thought I'd do is try to set the value of that cell just once a day through a script. I'm not at all familiar with writing Google Sheet scripts or calling them from a cell, and certainly not setting them to run at a given time. Any specific guidance would be appreciated. To be clear, the result of the script should be a simple number, one through twelve, identifying the current month number, and the associated cell should only be updated once a day.

0

Instead of NOW() you could try TODAY(). It's still a volatile function but its result will change only once per day.

If you anyway go for a script, use a time-driver installable trigger.

NOTES:

  1. To learn tha basics about adding a script to a spreadsheet see https://developers.google.com/apps-script/guides/sheets
  2. If you use a bounded script you could use SpreadsheetApp.getActive() to get the spreadsheet that contains the Apps Script project, otherwise you should use SpreadsheetApp.openByid(id) or SpreadsheetApp.openByUrl(url) where id or url are the id / url of your spreadsheet.

Example:

The following is an function to be called by a time-driven trigger that will add the current month number (1-12) to Sheet1!A1.

function respondeToDailyTrigger(){
  var now = new Date(); // Similar to Google Sheets NOW();
  var jsMonth = now.getMonth(); // 0 for January, 1 for February, and so on
  var spreadsheet = SpreadsheetApp.getActive(); // destination spreadsheet
  var sheet = spreadsheet.getSheetByName('Sheet1'); // destination sheet
  var range = sheet.getRange('A1'); // destination cell
  range.setValue(jsMonth + 1);  // add the month number (1-12) to the destination cell
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.