2

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

1

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

| improve this answer | |

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.