CODEWITHSUNDEEP
htmlgoogle-sheetspage-refreshgoogle-apps-script-addon
Awais Anwar
Awais Anwar

Reputation: 31

Stop Googe Sheet from refreshing HTML data

I have a sheet that pulls alot of finance data from alot of web pages using HTML. Problem is that it slows down alot. Plus it starts giving errors due to high number of HTMLs and etc.. i looked for the following solutions:

  1. Having an app script to make refreshes controlled..(not so effective)
  2. Any way i could copy paste the old data automatically till new data comes in and updates old pasted values..(also not effective as i couldnt find an automated method)
  3. Tried finding a way to play with google sheet auto-calculations to help....(also failed in that)

IS there a way to ristrict the sheet from auto refreshing so many times...?

Upvotes: 1

Views: 1499

Answers (2)

Alessandro
Alessandro

Reputation: 2998

In the documentation you can see that:

Functions that pull data from outside the spreadsheet recalculate at the following times:

  • ImportRange: 30 minutes
  • ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour
  • GoogleFinance: may be delayed up to 20 minutes

If you want to recalculate the value for your importHTML functions with a lower frequency you should definitely use Apps Script to do the data fetch and then populate your Spreadsheet with the information.

You can use UrlFetchApp class on Apps Script to get the data and define your timing logic for the updates.

function myAppScriptFunction() {

    var urls = ["your-website-url1", "...", "your-website-urlN"];
    var response = UrlFetchApp.fetchAll(urls);
    //... Parse the response and store the information you need in a table-like data structure

    // Let's assume the variable parsedResponseData is created
    var ss = SpreadsheetApp.getActiveSheet();
    ss.getRange("your-range").setValues(parsedResponseData);

}

You should now wrap your function in your custom time logic to manage the updates. You can achieve this with a time-driven trigger on Apps Script.

function triggerSetup() {
    ScriptApp.newTrigger('myAppScriptFunction')
          .timeBased()
          .everyHours(6)
          .create();
}

References:

Time-driven triggers

UrlFetchApp

Quota Limits

Upvotes: 1

player0
player0

Reputation: 1

you may try this addon which freezes your spreadsheet on demand:

https://gsuite.google.com/marketplace/app/spreadsheet_freezer/526561533622

0

Upvotes: 0

Related Questions