Search code examples
google-sheetsimport

How to save previous fetched data through importhtml or other functions in google sheets when they stopped working or gets error?

I have prepared a google sheet, I fetch external data in {A3:J} using importhtml function, and save it in {L3:U}.

The problem is that, when the error '#N/A' gets during fetching the data in {A3:A} (Pic of the error), previous fetched/stored data in {L3:U} also gets deleted and shows '#N/A'. So, please help/suggest me any command/formula/function to remains store previous fetched data in {L3:U} and automatically replace {L3:U} the data when new update/refresh.

I need to remains save the latest updated data in {L3:U} when gets error. sheet attaches.

Solution

  • I believe your goal is as follows.

    • You have a formula in the cell "A3".
    • When the formula is run, the table is imported to columns "A" to "J".
    • You want to copy the imported values to columns "L" to "U" without the formula.
    • You want to keep the imported values even when the formula returns #N/A in the cell "A3".

    In this case, how about the following sample script?

    Sample script:

    Please copy and paste the following script to the script editor of Spreadsheet and save the script. When this script is run, when the cell "A3" is not #N/A, the values are copied from "A3:J" to "L3:U" without the formula. When the cell "A3" is #N/A, the values are not copied. By this, the previous values are kept.

    function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const checkError = sheet.getRange("A3").getDisplayValue();
  if (checkError != "#N/A") {
    const srcRnge = sheet.getRange("A3:J" + sheet.getLastRow());
    srcRnge.copyTo(sheet.getRange("L3"), { contentsOnly: true });
  }
}
    • About Imported data is continuously changing after certain interval as fetched by Google sheet, in this case, how about running the above script by the time-driven trigger? Ref By this, the values are periodically updated.

    Note:

    • This script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

    Reference: