4

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)
2

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});
}
  • Thank you so much for the information, Minestrone. This worked to perfection. I was also wondering if there was a way to just import a single row into the log sheet, instead of the entire table? – Isaac Brekke Jan 28 '16 at 3:12
  • This may be possible with importxml. Which row do you want? – user79865 Jan 28 '16 at 3:23
  • Just the first row. – Isaac Brekke Jan 28 '16 at 4:05
  • I added this version to the answer. – user79865 Jan 28 '16 at 4:20
  • Bear in mind that IMPORTHTML is recalculated in intervals of 1 hour. See support.google.com/docs/answer/58515?hl=en – Rubén Jan 28 '16 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.