An issue has been bothering me. My Google Sheets has two columns of data, G and L. The data in G changes every 5 minutes, while the data in L does not change. The current requirement is to use a app script trigger to check every five minutes: if the G column of the same row is greater than the L column, then copy the data in G column to the L column of the same row. How can I implement this in the app script? Thanks!
Currently, I can only copy the entire G column data to the L column every five minutes, but I can't compare them.
function copyGtoL() {
const x = SpreadsheetApp.getActive();
const y = x.getSheetByName('data');
y.getRange('G4:G').copyTo(y.getRange(12, 4), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}
I integrated the conditional statement to compare the two columns and used a for loop to apply the condition to every row.
Modified code:
function copyGtoL() {
const x = SpreadsheetApp.getActiveSpreadsheet();
const y = x.getSheetByName('data');
const columnG = y.getRange(4, 7, y.getLastRow() - 3, 1).getValues();
const columnL = y.getRange(4, 12, y.getLastRow() - 3, 1).getValues();
for (let i = 0; i < columnG.length; i++) {
if (columnG[i][0] > columnL[i][0]) {
y.getRange(i + 4, 12).setValue(columnG[i][0]);
}
}
}
NOTE: Please use a Time-driven installable trigger to meet the requirement of 5 minutes update.
Result:
References: