google-sheetscopy-pasteappscript

Google sheets App script copy and paste with condition


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!

enter image description here

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);
}

Solution

  • Use conditional statement

    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:

    enter image description here

    References: