Google Script to set the current month once a day only in a cell

Question

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.

Answer 1

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
}