Google Spreadsheet automatic recalculate?

  • 1
  • Question
  • Updated 1 year ago
  • Answered
Is there a way to have a Google Spreadsheet recalculate when it's not open?

For instance, making a simple "Days since last accident" presentation. I enter the date of the last accident in a cell and in another cell subtract that date from Today().

The presentation will not show the correct number when the date changes unless you manually go into the spreadsheet and save it. Is there any way to automate this?

Thanks,
Stan
Photo of USCS

USCS

  • 408 Points 250 badge 2x thumb

Posted 5 years ago

  • 1
Photo of Neal

Neal

  • 61,252 Points 50k badge 2x thumb
I found this as a possible solution: https://productforums.google.com/foru...

Please let us know if this works for you!

Thanks.
Photo of USCS

USCS

  • 408 Points 250 badge 2x thumb
Looks like the solution offered in that thread no longer applies. GOOGLECLOCK() is no longer a valid function. The spreadsheet directs you to use the NOW() function, which does not update automatically.

Trying to figure out how to write a script to just increment an integer in a single cell in the spreadsheet and use a time based trigger. Seems like it should be easy, but have to figure out the syntax. Programming skills are woefully lacking. :-(
Photo of USCS

USCS

  • 408 Points 250 badge 2x thumb
Found a solution that works for now, it's a bit hokey but seems to work. Using the script below I write an arbitrary number '1' to a predetermined cell 'B1' in a selected sheet 'Support'. (The data I'm pulling for the presentation is in another sheet in the spreadsheet.) I use a time-driven trigger to execute the script at whatever interval I set. Every time the script is run it refreshes all the formulas in the sheets in the spreadsheet and saves the results.

It apparently doesn't matter that I'm not actually changing the value in the cell, the spreadsheet sees it as an update and does the refresh/save. I can watch it happening live if the spreadsheet is open. The script runs whether the spreadsheet is open or closed.

If you are refreshing a large complex sheet, this is kind of a sledgehammer approach, there might be a way to be more precise in what is being refreshed. I don't know.

function myRecalculate() {

// The code below opens a spreadsheet using its ID
// Note that the spreadsheet is NOT physically opened on the client side.
// It is opened on the server only (for modification by the script).
var ss = SpreadsheetApp.openById("Put_Your_Spreadsheet_ID_Here");
var sheet = ss.getSheetByName("Support");
var cell = sheet.getRange('B1')

cell.setValue(1)

}
Photo of Newark Fire

Newark Fire

  • 132 Points 100 badge 2x thumb
Thank you, I had a similar issue with updating.  I am just easing in to scripts and this did the trick.
Photo of Neal

Neal

  • 61,252 Points 50k badge 2x thumb
Thanks for sharing! Good to hear there is a solution that works for you.
Photo of AdGators AdGators

AdGators AdGators

  • 3,612 Points 3k badge 2x thumb
I've attempted the above solution without success.

I am looking to have a Google Spreadsheet that is running an importData function and updating it's source content within every 5min.

- People have tried the =GoogleClock style work around, UPDATE:Google is moving away from the =GoogleClock to the =Now() function, however, they've also included this functionality as a button in the 'new' version of Google Sheets (versus old Spreadsheets branding) located in "File > Spreadsheet Settings...", with settings to recalculate the spreadsheet every change and 5min. These don't seem to work, and my presentation sticks with old data.

- Another option I have found is the following Google Script recommendation through another forum:

function myFunction() {
var url = 'ExampleURL.csv';
var text = UrlFetchApp.fetch(url).getContentText();
var csv = Utilities.parseCsv(text);
return csv;
}

However, adding a trigger to execute this script every minute doesn't seem to do the trick either (in new or old spreadsheets)

To see where the holdup is occurring, I am able to view my Execution Transcript and witness the code above...oddly enough, it returns the up-to-date data....so it's working properly.

The only way I can get my data to fully update to the latest csv file that I'm hosting my my own server is by removing a part of the cell's data...if I remove 1 letter of the =importData("url.csv") function, and then re-add it...it refreshes properly. The same cell updating style works for the Script as well where =myFunction() would be removed and then re-added.

It appears to me that Google is either actively running some type of cell cache/delay in their new/old software...as they are very sketchy about how they are rolling out this new Google Sheets version*, so parts of it may already be in full swing. Or perhaps it's simply a glitch that has surfaced with this new update and they are unaware of the issue. In either regard I am stumped at the moment and hoping to find some assistance with the Rise Vision network.

As always, thank you to everyone who's ever participated in these forums...they are very useful.

*https://support.google.com/docs/answe...
Photo of David Wolfe

David Wolfe

  • 690 Points 500 badge 2x thumb
I think I've solved this, at least for my case. I have a Google spreadsheet exactly like described, and used for the same purpose. Since I only need the signage to display the contents of the single cell containing the number of days, I use IFTTT.com to add a new row at the bottom of the spreadsheet at the same time every day, and containing just the date. It seems that when IFTTT adds the row, it must open the spreadsheet, because the number does increment. It's been running for three days now and so far, so good.
Photo of Steven Sousa

Steven Sousa

  • 420 Points 250 badge 2x thumb
Dave sounds like it might be just what Im looking for - Would you mind telling me which IFTTT you used.  Im in the same boat and cant get the today() function to stay updated each day (it takes a minute to update now via the google sheet settings) I need to have it set prior to opening in the morning.  Which IFTTT did you use
Photo of David Wolfe

David Wolfe

  • 690 Points 500 badge 2x thumb
Sure. The trigger "IF THIS" is Time (at 9:00 am every day). The action "THEN THAT" is Google Drive: Add row to spreadsheet (add Check time as new row). I suspect I could have IFTTT add anything to the spreadsheet and it wouldn't make any difference. It's simply that fact that IFTTT is opening the spreadsheet and closing it again that forces my day counter to update. Hope this helps out.
Photo of Steven Sousa

Steven Sousa

  • 420 Points 250 badge 2x thumb
Thanks Dave
Photo of David Wolfe

David Wolfe

  • 690 Points 500 badge 2x thumb
I have come across an even better solution that more directly answers the question asked in the OP. Turns out you CAN calculate a Google Sheet that's not open. In the Sheet choose File > Spreadsheet settings . . . . In the resulting dialog, click the Calculation tab. From the Recalculation menu, choose "On change and every hour."

Using this, I was able to delete my IFTTT setup and eliminate dozens (or hundreds) of rows of dates in my sheets. 
Photo of Steffen Ruefer

Steffen Ruefer

  • 60 Points
But it does not show the new version if it was embedded or shared as link :(
Photo of Matthew

Matthew

  • 970 Points 500 badge 2x thumb
Isn't there just a setting that automatically re-calculates values every minute or hour?
http://prntscr.com/geq482