// Set your variables below
SHEET_NAME="MC-Import-Data"
URL="https://coinmarketcap.com/currencies/views/all/"
// Create a trigger to refresh every 5 minutes
function myFunctionA() {
ScriptApp.newTrigger("getData")
.timeBased()
.everyMinutes(5)
.create();
}
// Create a trigger to refresh every time the sheet is reopened
function myFunctionB() {
ScriptApp.newTrigger("getData")
.fromSpreadsheet()
.open()
.create();
}
// Compile your variable URL with an appended data-time stamp
function getData() {
SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName(SHEET_NAME)
.getRange("A1")
.setValue( URL + "?" + Utilities.formatDate(new Date(), "GMT", "yyyyMMddHHmmss") );
}
// Upon completion, you will get an updating URL in A1 of the sheet you specified in the URL= variable
// Be sure to open your sheet and use the updating URL to build your IMPORTHTML formula
// Example: =IMPORTHTML(A1,"TABLE")
Forked from ranaroussi/Google docs cache-busting importdata.md
Last active
June 15, 2023 16:16
-
-
Save willinspire/129ca95babbcf3e31cc9bee2d15b87cc to your computer and use it in GitHub Desktop.
It is not easy to refresh the IMPORTHTML functions in Google Sheets due to cashe limitations. The resulting limitations are old data instead of new data being pulled into the Sheet upon running a Script to reload the IMPORTHTML function. This Google Script is a "Cache-Busting" function which circumvents this problem.
This worked great! Thank you very much!
Thanks for this. I thought I was being fed old data because my trigger ran too often. Happy this was such an easy fix!
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I must be doing something wrong but I'm getting the following error:\
TypeError: Cannot call method "getRange" of null. at getData(Code:27)
I don't fully understand how to compile URL variable for getData function