How to copy the last row in one table and append to another table in the same GSheet (the same tab)

Question

I have a GSheet. It has 3 tables: Blue, Purple, Green

1. Blue table gets data automatically from the other source; So, every day that table will get bigger automatically. Nothing to do with that

2. Purple table gets data from the Blue table automatically using UNIQUE and QUERY. Nothing to do with that as well

3. Green table needs a script. The script will have a trigger (lauch once per week). All it has to do - copy the last row from the Purple table and append to the Green table.

below is the GSheet link https://docs.google.com/spreadsheets/d/1lv4VytZu8uI1OYX_FjR5aH4YM8CX7PCSr5Uuk_bViD8/edit?gid=1875452837#gid=1875452837

function copyData() {
  const ss = SpreadsheetApp.getActive();

  // get the source and target sheets
  const ssh = ss.getSheetByName("TestSheet");
  const tsh = ss.getSheetByName("TestSheet");

  //get the data from the source sheet
  const data = ssh.getRange("I"+ssh.getLastRow()+":K"+ssh.getLastRow()).getValues();

/**
* @type {any[]}
*/
var r;

  //select the required columns (the index starts from 0)
  const fdata = data.map(r=>[r[0],r[1],r[2]]);

  //paste the data to the target sheet starting from the last row with content
 //?????????????????????????
  }

}

This is what I've made so far, but I can't finish the script and I'm not sure is the "get data" part correct at all also.

Answer 1

Append Last Row with Apps Script and Set Up Weekly Trigger

You can test the code below to see how it transfers data from the Purple table to the Green table, making sure the most recent row is appended correctly and handling empty rows and the end of the data range.

Code:

function copyData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('TestSheet');
  var purpleRange = sheet.getRange('I3:K19');
  var greenRange = sheet.getRange('M2:O19');

  var purpleData = purpleRange.getValues();
  var lastRowIndex = purpleData.length - 1;
  while (lastRowIndex >= 0 && purpleData[lastRowIndex].join("") === "") {
    lastRowIndex--;
  }

  var lastRow = purpleData[lastRowIndex];
  var greenData = greenRange.getValues();
  var emptyRowIndex = greenData.findIndex(row => row.every(cell => cell === ''));

  if (emptyRowIndex === -1) {
    emptyRowIndex = greenData.length;
  }

  var greenRowIndex = emptyRowIndex < greenData.length ? emptyRowIndex : greenData.length;
  greenRange.getCell(greenRowIndex + 1, 1).setValue(lastRow[0]);
  greenRange.getCell(greenRowIndex + 1, 2).setValue(lastRow[1]);
  greenRange.getCell(greenRowIndex + 1, 3).setValue(lastRow[2]);
}

If your data expands and the table range changes, you might need to adjust this part of your code to fit the new range.

      var purpleRange = sheet.getRange('I3:K19');
      var greenRange = sheet.getRange('M2:O19');

Here are the steps to set up a Trigger:

To run this script once per week, you need to set up a trigger in Google Sheets:

1.) In the Apps Script editor, click on the clock icon on the left toolbar to open the triggers page.

2.) Click + Add Trigger at the bottom-right.

3.) Set the function to copyData.

4.) Set the deployment to Head.

5.) Choose "Time-driven" for the event source.

6.) Choose "Week timer" for the type of time-based trigger.

7.) Select the desired day and time for the trigger to run.

8.) Save the trigger, and your script will now run automatically once per week.

Additional image for visualizing the setup of Time driven triggers:

Sample Output:

Reference:

Time-driven triggers