Prepend fow on top of a table in Google Sheets

Question

I am keeping track of my stock portfolio in Google Sheets. The script below is triggered every evening after markets close. It takes certain values and writes them to a sheet called Portfolio, where I keep a history of these metrics over time.

var currSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var shtPortfolio    = currSpreadsheet.getSheetByName('Portfolio');

function savePortfolio()
{  
  var rngMarketValue     = shtPortfolio.getRange("G3:G3").getValues();
  var rngCost            = shtPortfolio.getRange("G2:G2").getValues();
  var rngUnrealizedPL    = shtPortfolio.getRange("L2:L2").getValues();
  var rngUnrealizedYield = shtPortfolio.getRange("K3:K3").getValues();
  var rngRealizedPL      = shtPortfolio.getRange("J2:J2").getValues();
  var rngRealizedReturn  = shtPortfolio.getRange("L6:L6").getValues();
  var rngDividends       = shtPortfolio.getRange("N2:N2").getValues();
  var rngYield           = shtPortfolio.getRange("M3:M3").getValues();

  const d = new Date();
  const mn = new Intl.DateTimeFormat('en', { minute: '2-digit' }).format(d);
  const hr = new Intl.DateTimeFormat('en', { hour: '2-digit', hour12: false }).format(d);
  const ye = new Intl.DateTimeFormat('en', { year: 'numeric' }).format(d);
  const mo = new Intl.DateTimeFormat('en', { month: '2-digit' }).format(d);
  const da = new Intl.DateTimeFormat('en', { day: '2-digit' }).format(d);
  
  var arrNewRow = ['', // column A always empty
      (`${da}.${mo}.${ye} ${hr}:${mn}`),
      rngMarketValue[0][0],
      rngCost[0][0],
      rngRealizedPL[0][0],
      rngRealizedReturn[0][0],
      rngUnrealizedPL[0][0],
      rngUnrealizedYield[0][0],
      rngDividends[0][0],
      rngYield[0][0]
  ];
  shtPortfolio.appendRow(arrNewRow);
}

It works pretty well, but it is bothering me that the newest days, are at the bottom of the sheet. I would like to do the reverse: add the newest entry in a row on top of the sheet, as follows:

This is what the log looks like now. Please note that the history that I'm keeping only starts on row 4; the rows above have stuff in them that needs to stay there.

    A     B           C               D           E
1         Here I have some stuff written that needs to stay on top of the sheet
2
3         Date        MarketValue     Cost ...    RealizedPL
4         14.01.2021  4500            4000        100
5         13.01.2021  4600            3900        100

After today, the script would grab the values as always, but now create a new row ABOVE row 4 (instead of just appending in row 6):

    A     B           C               D           E
1         Here I have some stuff written that needs to stay on top of the sheet
2
3         Date        MarketValue     Cost ...    RealizedPL
4         15.01.2021  4300            4000        100
5         14.01.2021  4500            4000        100
6         13.01.2021  4600            3900        100

Can this be done, and if so, can someone help me out? I was looking for a function called prepend but it does not seem to exist.

Answer 1

You can use insertRowBefore(beforePosition) to add a new row before a specific row index.

After that, select the range in the newly added row where you want to set the values of your data.

Sample:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var data = [[new Date(),100,100,100] ];
sheet.insertRowBefore(4);
sheet.getRange(4,2,1,4).setValues(data);

Output:

Answer 2

Yes, that can be done and quite a few different ways. Here is my suggestion. I made a few changes to your script. Instead of getting several different values from the spreadsheet 1 at a time, you can grab all of them at once (saves time.) Also, your date formatting can be done with one line.

 var currSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var shtPortfolio    = currSpreadsheet.getSheetByName('Portfolio');

function savePortfolio(){

  var allData = shtPortfolio.getDataRange().getValues();
  var rngMarketValue = allData[2][6];
  var rngCost = allData[1][6];
  var rngUnrealizedPL = allData[1][11];
  var rngUnrealizedYield = allData[2][10];
  var rngRealizedPL = allData[1][9];
  var rngRealizedReturn = allData[5][11]
  var rngDividends = allData[1][13];
  var rngYield = allData[2][12]

  var formattedDate = Utilities.formatDate(new Date(), "GMT", "dd.MM.yyyy HH:mm");
  
  var arrNewRow = ['', // column A always empty
      formattedDate,
      rngMarketValue[0][0],
      rngCost[0][0],
      rngRealizedPL[0][0],
      rngRealizedReturn[0][0],
      rngUnrealizedPL[0][0],
      rngUnrealizedYield[0][0],
      rngDividends[0][0],
      rngYield[0][0]
  ];
  shtPortfolio.appendRow(arrNewRow);
  shtPortfolio.getRange(4, 1,shtPortfolio.getLastRow()-3,arrNewRow.length).sort({column: 2, ascending: false});
}

All you need to do is sort the range when you append the new row. You could insert a row above row 4 and then add the info but I noticed that would mess with your rngRealizedReturn cell data location. So I opted to sort the range after it was appended to the data. Hope this helps!