Get updated with 2019-nCoV (or any) real-time statistics with Google Sheets and Apps Script [2/3]
Step 2: Add updated data into new rows
After importing the online data, we probably want to log data retrieved before they got flushed by new data.
Writing a data logging function
A user provided a nice solution on Stack Overflow to store past values regularly or upon value changes. It makes use of the Google Apps Script integration functionality. You can go to “Tool > Script Editor” to code for the desired action.
I adapted the code as follows:
var id = "xxx"; //copy id from URL https://docs.google.com/spreadsheets/d/xxx/function storeValue() {
var ss = SpreadsheetApp.OpenById(id);
var sheet = ss.getSheetByName('updating'); // where the IMPORTXML function locates
var values = sheet.getRange("A2:E2").getValues()[0];
var sheet2 = ss.getSheetByName('log'); // where to store the data
var height = sheet2.getLastRow();
sheet2.insertRowAfter(height);
sheet2.getRange(height+1, 1, 1, 6).setValues([[new Date()].concat(values)]);
}
I named the above working sheet as “updating”, and added another sheeted called “log”. I put the same header to “log”, but it does not affect the updating whether you do so or not.
The code first find the source sheet by name, get values from specified cells into an array, find the ID of the last filled row on target sheet, and fill the next row with retrieval timestamp and copied values.
To make the code work, you need to first grant access to your Google Sheets and allow Apps Script to work with Google Sheets API. You will be prompted to authorize access to files before reaching Apps Script. Then in the Script Editor, select “Resources > Advanced Google Services” and turn on the API as follows.
Setting trigger to run
Now, after running the code by pressing ▷ twice, we have two rows of logged entries.
We can then set to trigger the function by time or value change events by clicking the timer button.
It will lead you to a new tab where you can add a trigger by clicking the + button on bottom right.
(Forgive me for the Japanese, too lazy to switch back and forth. It basically says トリガー Trigger, オーナー Owner, 前回の実行 Last Executed, 導入 Deployment, エベント Event, 関数 Function, and エラー率 Error Rate)
When you add or edit a trigger, a panel will pop up to let you select the function to trigger, when to deploy, to trigger by time or event, and how frequent will you receive error notifications.
(Davise Ford wrote a method to force refresh at a specified time, if you absolutely want to do so between minutes.)
To show the effect, I set it to log per minute, and we see a jump in each value already 😢
References
[3] ImportXML using Google Spreadsheets and Save Results Over-Time
https://stackoverflow.com/questions/37490561/importxml-using-google-spreadsheets-and-save-results-over-time
[4] Force IMPORTXML (Google Spreadsheets) to Refresh
https://daviseford.com/blog/2017/05/04/importxml-force-refresh.html
Please clap here as well if you like my post :)
Disclaimer: The article only represents personal ideas. This disclaimer informs readers that the views, thoughts, and opinions expressed in the text belong solely to the author, and not necessarily to the author’s employer, organization, committee or other group or individual.
