How to make google spreadsheet refresh itself every 1 minute

Question

My google spreadsheet is using GOOGLEFINANCE  symbol   price  function to retrieve stock prices of my portfolio  Unfortunately  I have to refresh manually now  How can I make the spreadsheet refresh itself every 1 minute   Thank you for your help

User · Accepted Answer

If you re on the New Google Sheets  this is all you need to do  according to the docs      change your recalculation setting to  On change and every minute  in your spreadsheet at File   Spreadsheet settings    This will make the entire sheet update itself every minute  on the server side  regardless of whether you have the spreadsheet up in your browser or not   If you re on the old Google Sheets  you ll want to add a cell with this formula to achieve the same functionality    GoogleClock     EDIT to include old and new Google Sheets and change to  GoogleClock

User · Answer

I had a similar problem with crypto updates  A kludgy hack that gets around this is to include a    now   - now    stunt at the end of the cell formula  with the setting as above to recalculate every minute  This worked for my price updates  but  definitely an ugly hack

User · Answer

If you are only looking for a refresh rate for the GOOGLEFINANCE function  keep in mind that data delays can be up to 20 minutes  per Google Finance Disclaimer    Single-symbol refresh rate  using GoogleClock   Here is a modified version of the refresh action  taking the data delay into consideration  to save on unproductive refresh cycles    GoogleClock GOOGLEFINANCE symbol  datadelay      For example  with     SYMBOL  GOOG DATA DELAY  15  minutes    then   GoogleClock GOOGLEFINANCE  GOOG   datadelay      Results in a dynamic data-based refresh rate of    GoogleClock 15    Multi-symbol refresh rate  using GoogleClock   If your sheet contains a number of rows of symbols  you could add a datadelay column for each symbol and use the lowest value  for example    GoogleClock MIN dataDelayValuesNamedRange     Where dataDelayValuesNamedRange is the absolute reference or named reference of the range of cells that contain the data delay values for each symbol  assuming these values are different    Without GoogleClock    The GoogleClock   function was removed in 2014 and replaced with settings setup for refreshing sheets   At present  I have confirmed that replacement settings is only on available in Sheets from when accessed from a desktop browser  not the mobile app  I m using Google s mobile Sheets app updated 2016-03-14     This part of the answer is based on  and portions copied from  Google Docs Help   To change how often  some  Google Sheets functions update    Open a spreadsheet  Click File   Spreadsheet settings  In the RECALCULATION section  choose a setting from the drop-down menu  Setting options are     On change On change and every minute On change and every hour  Click SAVE SETTINGS     NOTE External data functions recalculate at the following intervals    ImportRange  30 minutes ImportHtml  ImportFeed  ImportData  ImportXml  1 hour GoogleFinance  2 minutes   The references in earlier sections to the display and use of the datadelay attribute still apply  as well as the concepts for more efficient coding of sheets    On a positive note  the new refresh option continues to be refreshed by Google servers regardless of whether you have the sheet loaded or not  That s a positive for shared sheets for sure  even more so for Google Apps Scripts  GAS   where GAS is used in workflow code or referenced data is used as a trigger for an event       in my understanding so far  I am currently testing this

User · Answer

GOOGLEFINANCE can have a 20 minutes delay  so refreshing every minute would not really help  Instead of GOOGLEFINANCE you can use different source  I m using this RealTime stock prices I tried a couple but this is the easiest by-far to implement  They have API that retuen JSON   Name  CurrentPrice   Here s a little script you can use in Google Sheets Tools- gt Script Editor  function GetStocksPrice              var url    https   financialmodelingprep com api v3 stock real-time-     price AVP BAC CHK CY GE GPRO HIMX IMGN MFG NIO NMR SSSS UCTT UMC ZNGA      var response   UrlFetchApp fetch url          convert json string to json object    var jsonSignal   JSON parse response             define an array of all the object keys   var headerRow   Object keys jsonSignal        define an array of all the object values   var values   headerRow map function key   return jsonSignal key          var data   values 0         get sheet by ID -        you can get the sheet unqiue ID from the your current sheet url   var jsonSheet   SpreadsheetApp openById  quot Your Sheet UniqueID quot          var name   jsonSheet getName       var sheet   jsonSheet getSheetByName  Sheet1           the column to put the data in - gt  Y   var letter    quot F quot         start from line    var index   4    data forEach function  row  index2            var keys   Object keys row        var value2   row keys 1                         set value loction      var cellXY   letter    index             sheet getRange cellXY  setValue value2             index   index   1                Now you need to add a trigger that will execute every minute   Go to Project Triggers - gt  click on the Watch icon next to the Save icon Add Trigger In - gt  Choose which function to run - gt  GetStocksPrice In - gt  Select event source - gt  Time-driven In - gt  Select type of time based trigger - gt  Minutes timer In - gt  Select minute interval - gt  Every minute  And your set

User · Answer

use now   in any cell  then use that cell as a  dummy  parameter in a function  when now   changes every minute the formula recalculates  example  someFunction a1 b1 c1     cell with now     cell with now

[google-apps-script] How to make google spreadsheet refresh itself every 1 minute?

Examples related to google-apps-script

Examples related to google-sheets

Examples related to google-finance