IMPORTHTML at a certain time of day in Google Spreadsheets

Question

I am looking for a way to import the table from this formula once per day at a certain time instead of automatically updating as the formulas seems to do. I need to do this in order to track certain movements in the market every day.

=IMPORTHTML("http://www.cmegroup.com/trading/agricultural/grain-and-oilseed/corn.html","table",1)

Answer 1

If logging the entire table

I would keep importhtml in one sheet and use another sheet for historical log. Below these are Sheet1 and Sheet2.

function dailyLog() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getSheetByName('Sheet1');
  var logSheet = ss.getSheetByName('Sheet2');
  var lastRow = logSheet.getLastRow();
  logSheet.getRange(lastRow + 1, 1).setValue(new Date());  // insert timestamp
  var range = currentSheet.getDataRange();
  range.copyTo(logSheet.getRange(lastRow + 2, 1), {contentsOnly: true});
}

This script should be set to run daily (in Script Editor, go to Resources > This project's triggers, and create a daily trigger for it).

The script copies the data range of Sheet1 to the end of the data range in Sheet2, keeping the contents only (an equivalent of manual action Ctrl-Shift-V).

If logging the first row of the table

First, wrap your importhtml() command in array_constrain(..., 2, 99) so that only two rows of the table are present in the sheet: the headers and the first data row.

Then schedule the following function to run daily. It takes the second row of the sheet and copies it below, within the same sheet.

function dailyLog() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var range = sheet.getRange(2, 1, 1, lastColumn);
  range.copyTo(sheet.getRange(lastRow+1, 1), {contentsOnly: true});
}