Google-sheets – Importing a value from a webpage, and storing it as value, not a function

google sheets

I am writing a spreadsheet that imports values from webpages that change a lot, I want to keep note on these values over the days of a week. However I'm unsure of how to note them at certain times.

I have written this so far but am unsure it it will stay.

https://docs.google.com/spreadsheets/d/1tHyteA6tSy1PIzGLo3eYobfUmNOqmC2k3BNREOBscPI/edit?usp=sharing

Best Answer

Your attempted set of formulas cannot work as intended. You have the ImportHTML function in cell B1, a list of dates in column A and commands such as

=IF(TODAY()=A6,$B1)

in column B. An issue with this formula is that the condition will become FALSE after the day passes, so the data will be lost. And trying to rectify with =IF(TODAY()>=A6,$B1) would not help, since then the data will keep on changing.

The basic limitation here is that a formula cannot stop being a formula on its own. You want to "freeze" it in place, replacing with a static value. Apart from manual editing, this can only be done with a script. Here is a script that produces a record in the form similar to yours:

function addDateAndValue() {
  var url = ' url of your spreadsheet ';  // put url here
  var ss = SpreadsheetApp.openByUrl(url);
  var sh = ss.getSheetByName('Sheet1');   // or another name
  var height = sh.getDataRange().getHeight();
  sh.insertRowAfter(height);
  sh.getRange(height+1, 1).setValue(Utilities.formatDate(new Date(), "GMT", "mm/dd/yyyy"));
  sh.getRange(height+1, 2).setValue(sh.getRange("B1").getValue());
}

Namely, it adds a new row at the bottom of the current data range, puts today's date in column A and puts the value from B1 into column B. The copied value is just that, a number (or string), which is not going to change anymore.

You can make this script run daily by adding a trigger via (Resources > Current project's triggers).

Related Topic