CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
2badatcoding
2badatcoding

Reputation: 115

Google Sheets: leaving a cell value unchanged If importhtml returns a #N/A error

I'm trying to write a custom function to prevent the value of a cell from changing if the importhtml returns a #n/a error.

This formula summarize the idea: =IF(ISNA(IMPORTHTML("http://www.example.com","table",1)), "do not change cell value", IMPORTHTML("http://www.example.com","table",1))

I thought about saving the cell value before executing the importhtml function, so I could put the saved value back if the importhtml returns a #n/a error.

I've been testing a lot of different codes, but always got some error as a result.

I will be very grateful if anyone can help me.

Upvotes: 0

Views: 320

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

  • Create a secondary sheet
  • Copy-paste once values only from the primary sheet with the importhtml formula to the secondary sheet
  • Write a script that will copy all the values from the primary to the secondary sheet that are not NaN
  • Bind to the script an installable onChange trigger that will run the script each time an update of IMPORTHTML takes place

Sample script:

function onChange() {
  var ss=SpreadsheetApp.getActive();
  var importSheet=ss.getSheetByName("Sheet1");
  var finalSheet=ss.getSheetByName("Sheet2");
  var importRange=importSheet.getDataRange();
  var data=importRange.getValues();
  for(var i=0; i<data.length;i++){
    for(var j=0; j<data[0].length;j++){
      if(!isNaN(data[i][j])){
        finalSheet.getRange(i+1, j+1).setValue(data[i][j]);
    }
  }
 }      
}

Upvotes: 2

Related Questions