0

I would like to add a row every day at midnight to my Google Sheet containing two columns: 1st the current date, 2nd the cell value from another sheet (portfolio value).
I.e.: I want to make a graph to see the development of my stock portfolio value over time.
I read somewhere that you could do it with the script editor, but couldn't find a good enough example for me to understand.

0

You want to insert a new row once a day (at midnight), and insert a timestamp in column A, and the value of another cell in Column B.

It would be unusual to find a exact precedent for this scenario, but identifying the various elements simplifies the task.

1 - every day at midnight

This is done by adding a Time-driven trigger to the script. "Day Timer", and the time of day "Midnight to 1am".

2 - add a new row

There is a specific method for this: appendRow(rowContents) doc ref.

The advantage of this method is that it not only that it adda the new row, but updates the data at the same time.

3 - Column A = the current date

How to insert the current date into a Google Spreadsheet? - does what is says on the tin.

4 - Column B = the cell value from another sheet (portfolio value)

A couple of things:

  • getSheetByName(name): since the input and the output are on different sheets, you need to declare the location of each.
  • getRange(): declare the cell where the valuation can be found
  • getValue(): get the value of the cell containing the valuation.

function dailyvaluation() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // declare the source and target sheets
  var sheetnamePortfolio = "Portfolio"; // sheet containing the valuation
  var sheetnameDailyvalue = "DailyValue"// sheet to update daily
  var portfolio = ss.getSheetByName(sheetnamePortfolio);
  var daily = ss.getSheetByName(sheetnameDailyvalue);
  
  // declare the new date
  var newdate = new Date();
  // get the valuation
  // assume cell A1 of sheet="Portfolio"
  var value = portfolio.getRange(1,1).getValue();
  // Logger.log("DEBUG: the date is "+newdate+", and the value is "+value)
  
  // append the new row with the values
  daily.appendRow([newdate, value]);

}
1
  • Thanks a lot for your valuable answer! I managed to get it done with your suggestion! Merry Christmas! – pfra Dec 21 '20 at 15:38

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.