Google Spreadsheet automatic recalculate?

Comments

20 comments

  • Avatar
    Neal
    I found this as a possible solution: https://productforums.google.com/foru...

    Please let us know if this works for you!

    Thanks.
    0
    Comment actions Permalink
  • Avatar
    Ingram Micro
    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. :-(
    0
    Comment actions Permalink
  • Avatar
    Ingram Micro
    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)

    }
    0
    Comment actions Permalink
  • Avatar
    Nick Simmons
    Thank you, I had a similar issue with updating.  I am just easing in to scripts and this did the trick.
    0
    Comment actions Permalink
  • Avatar
    Neal
    Thanks for sharing! Good to hear there is a solution that works for you.
    0
    Comment actions Permalink
  • Avatar
    Joe Kirchner
    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...
    0
    Comment actions Permalink
  • Avatar
    David Wolfe
    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.
    0
    Comment actions Permalink
  • Avatar
    mortencopenhagen
    Hi David Wolfe. Would you care to share a few details on how you compose a URL call to your google spredsheet that will add a like to your sheet and hence force a recalculation to be done?  Cheers and thanks.
    0
    Comment actions Permalink
  • Avatar
    David Wolfe
    My solution didn't require any URL call or anything. I'm simply connecting my IFTTT.com account to my Google Drive with a recipe. The Channels I'm using are Date/Time and Google Drive. The Trigger is Date/Time {every day at . . .}, the Action is Add Row to Spreadsheet. Every day at the given time, the recipe triggers and adds a row containing only the date (this is configurable in IFTT) to the end of the spreadsheet. Every day when I come in and see our signs, the number in the cell I've put into the placeholder in my RV presentation has incremented without ever having to open the spreadsheet. I would imagine this would work for anything that you need to have recalculated daily.
    0
    Comment actions Permalink
  • Avatar
    mortencopenhagen
    Thanks for your super prompt reply. I will take an other look at IFTTT (I only used it on my phone so far to turn on my Phillips Hue lamps daily when it gets dark).  If needed I came to think about a form linked to my sheet. In forms you can get help to make a prefilled URL that also include a submit. That would be a way to get a specific URL that will fill some dummy data into a google sheet. Thanks again for your kind help.
    0
    Comment actions Permalink
  • Avatar
    David Wolfe
    IFTTT is free, very simple to use, and gives you a lot of control to link together multiple other services to do things automatically. I use it in a lot of my other RV presentations, too. For example, we bought a bt.tn wifi button that connects through IFTTT. When our drivers depart for their deliveries each night, they push the button. The button triggers 2 IFTTT recipes that 1) Send a tweet with the departure time and 2) add a line to a Google Spreadsheet that displays the previous 5 days departure times on a RV presentation.
    0
    Comment actions Permalink
  • Avatar
    mortencopenhagen
    Amazing!
    0
    Comment actions Permalink
  • Avatar
    HSuarez
    Hi David,
    Thanks for sharing to us your experience with IFTTT.  I was registered for that service when it was initially launched but I came back to check it out again after seeing your answer from last week.
    0
    Comment actions Permalink
  • Avatar
    mortencopenhagen
    I have finally solved my issue. I now use a linux cron job to make an http call to a specific url that adds some dummy date to my database. By doing so the entire sheet is recalculated. On http://justingale.com/2013/09/url-tricks-for-google-forms-pre-populate-and-automatically-submitting-... you can find how to make a prepopulated URL that will add some data via a form. Easy peacy!
    0
    Comment actions Permalink
  • Avatar
    Steven Sousa
    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
    0
    Comment actions Permalink
  • Avatar
    David Wolfe
    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.
    0
    Comment actions Permalink
  • Avatar
    Steven Sousa
    Thanks Dave
    0
    Comment actions Permalink
  • Avatar
    David Wolfe
    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. 
    0
    Comment actions Permalink
  • Avatar
    Steffen Ruefer
    But it does not show the new version if it was embedded or shared as link :(
    0
    Comment actions Permalink
  • Avatar
    Matthew Trigg
    Isn't there just a setting that automatically re-calculates values every minute or hour?
    http://prntscr.com/geq482
    0
    Comment actions Permalink

Please sign in to leave a comment.