Google-sheets – Monitor webpage changes using Google Spreadsheets

google sheetsimporthtml

I want to monitor a web page. The best solution I found was Google Spreadsheets
(apart from feed43 which has limitations).

I'm using the importHTML function in Google Spreadsheet which will update the cell value whenever there is any change in data on that web page.

However, I don't know how to save previous value somewhere and if possible append the new updated value to next row of the spreadsheet instead of overwriting the data on that cell.

Best Answer

Yes, it's possible to do that with a simple script such as this one. What it does:

  1. Check whether the current value is equal to the most recent "archived" value.
  2. If not, then archive the current value, inserting a row for this purpose.

After entering the script into the Script Editor, you should create a trigger for it: see Resources > This project's triggers. Two options are: running periodically, or running when the data changes. The second one is more natural for this task, but in case onChange fails to work for you (some people had issues in the past), timed trigger is an option.

function recordChanges() {
  var url = 'the URL of your spreadsheet';   // put URL here
  var ss = SpreadsheetApp.openByUrl(url);
  var sh = ss.getSheetByName('Sheet1');  // or whatever name
  var range = sh.getRange("A1:A2");    // assuming your data appears in A1 cell
  var values = range.getValues();
  if (values[0][0] != values[1][0]) {
    sh.insertRowAfter(1);
    sh.getRange("A2").setValue(values[0][0]);
  }
}

This can go on until you hit the limit of 200,000 cells in a spreadsheet... if the spreadsheet has one column, then it can have up to 200,000 rows, so even if another row is added every hour, it will last more than 20 years.