Google Sheets Script - Copying dynamic array values to static table

Question

I have a sheet where I am pulling in data with a script on some performance metrics, which updates daily. What I would like to do is copy the values from this dynamically updating range to a separate sheet and log them in rows as static values in descending order.

Sheet A

Yesterday's X, Y, Z

Sheet B

Yesterday's X, Y, Z Day Before X, Y, Z 3 Days Ago X, Y, Z

So that I can calculate a moving average on a 7-day+ basis.

What is the best way to accomplish this, accounting for the automatically updating range and the need to log the output from that in a separate, static range elsewhere?

Answer 1

You can get the rows from your dynamic sheet and append them into you static sheet. Something like this. Then you could add a daily trigger for this script to run daily.

function getDynamicRows(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('dynamicSheet');
  var sourceRange = sheet.getRange('A1:B');
  var data = sourceRange.getValues(); // Array of arrays [[Row1],[Row1],[Row3]]

  // add data to next empty row in the static sheet. 
  var targetSheet = ss.getSheetByName('targetSheet');
  data.forEach(function(row){
    targetSheet.appendRow(row)
  })


}