I have a GSheet. It has 3 tables: Blue, Purple, Green
Blue table gets data automatically from the other source; So, every day that table will get bigger automatically. Nothing to do with that
Purple table gets data from the Blue table automatically using UNIQUE and QUERY. Nothing to do with that as well
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.
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.
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');
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.