How to stop formulas from updating at a certain time each day in Google Sheets?

Question

I want Google Sheets to stop updating a formula in a cell corresponding to the current date at a certain time each day, but I am not well versed in scripting so I need some help. I have some examples of my data below.

I have a sheet "Time Spent in Statuses" that automatically updates every hour (with a google sheets extension). New 'Keys' are added to the sheet when they are added in the extension, i.e Key '5' doesn't exist now, but when someone creates it in the data source linked with the extension, Key '5' and its corresponding values will be automatically added, so it updates frequently with more data.

In a separate sheet, I track the daily averages of numbers from the auto update in a sheet "Daily Averages" .

Currently in the "Daily Averages" sheet, I use a formula to calculate the average of each column in the "Time Spent in Statuses" sheet. This formula is pre-filled for days upcoming. At the end of each day, I have to go to this sheet and Copy and Paste > Values Only to record the averages for that day and stop the formula from continuing to update the next day. I use the 'Date' column and the Avg columns to show change in the daily averages over time in a dashboard.

What I'm looking for is some kind of logic that will look at the 'Date' column in the "Daily Averages" sheet and if it equals the current date and the time is 11:59 pm (or some other set time), then automatically Copy and Paste > Values Only. Or something similar so that I don't have to go in and manually paste values only every day. Does anyone have any experience doing something like this?

Answer 1

What you're looking for is a time-driven trigger in Apps Script. You can write a script and set it to run daily at a specific time.

There are multiple approaches that you can take when writing the script, but based on the sample that you provided, you can try the following code:

function setDailyAverage() {
  let ss = SpreadsheetApp.getActiveSpreadsheet()
  let avgsheet = ss.getSheetByName("Daily Averages")

  //this is a string with today's date in format mm/dd/yyyy, like in your sample
  let today = new Date().toLocaleDateString("en-US")

  //this will do a search of today's date and return the row number
  let row = avgsheet.createTextFinder(today).findNext().getRow()

  let avgcell = avgsheet.getRange(row,2) //gets column 2 in the row corresponding to today
  let giavgcell = avgsheet.getRange(row,3) //gets column 3 in the row corresponding to today

  //This reads the values in each cell and overwrites the formula with the actual value
  avgcell.setValue(avgcell.getValue())
  giavgcell.setValue(giavgcell.getValue())

}

To add the script go to Extensions > Apps Script and paste it there, then on the left side go to Triggers > Add Trigger, then the trigger settings should look like this:

This would run the script every day between 11pm and midnight, and pretty much just automate the same "Copy and Paste > Values Only" process that you're following. You still would need to have the pre-filled rows for each date so keep that in mind. With the trigger you could also automate the creation of each row daily but I don't know if this would interfere with your workflow.