How to add row to Google Sheet daily with two columns?

Question

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.

Answer 1

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]);

}