Make weekly copy script

Question

I have a GSheet and I need a script there.

There are 3 tables there (each with 3 columns):

• Yellow table (columns AC-AE) is updated automatically every day; Nothing to do with it.
• Green Table gets data from the yellow table automatically (with FILTER function in cell Y3). So, the number of lines in the Green table can be different and it is unpredictable.

Goal of the script: once per week (Sunday evening) it should copy the green table (without header) and append to the Purple table (values only).

Below is the GSheet link: https://docs.google.com/spreadsheets/d/1fCPM16quoL21IQRfWE2eL9hlud8Rv21xVHDfiAVNFO4/edit?usp=sharing

This is what I did (but it does not work for some reason):

function logWeeklyData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Profession_Sheet');
  const data = sheet.getRange(3,25,dataSheet.getLastRow()-1, 3).getValues();
  sheet.getRange(sheet.getLastRow() + 1, 33, data.length, 3).setValues(data);
}

Answer 1

There are two issues that I see.

First dataSheet.getLastRow()-1 should be changed to sheet.getLastRow()-1. Otherwise you will get the following error:

dataSheet is not defined.

Second, in the example spreadsheet, I think you will find if you run your code with the above change, that it does indeed run properly, just not with the results that you expected. lastRow applies to the sheet, not the range in question. As a result, the data from the green table will get added to the purple table starting on row 65 since row 64 is your last row.

The simplest solution is to move the yellow table to its own sheet and then reference that sheet in your filter formula to populate the green table. Then go ahead and run your code and you should get the results you expected.

Then it is simply a matter of setting up the appropriate triggers to run the script when you want it to run.

Hope that helps!