How to Copy Flagged Rows from Source sheet and Update to Discontinuous Destination Rows with the source data in Google App Script

Question

Goal of the function:

Rows with a TOSync value of TRUE shall be copied and pasted over to the Destination sheet using the rowid in the source sheet.

The current sheet where the data is copied from can be filtered and sorted.

Source sheet contains a checkbox with a true/false value. True means it should be flagged for syncing/copying to destination sheet.

Row ID refers to these row numbers in the destination sheet.

Triggering the function should copy all rows flagged as TRUE/checked, then copy them over to the corresponding row index in the destination.

Example using picture above:

1. First flag has a row ID of 1741.
2. The function will copy the data range from source sheet (trackerSheet)
3. and paste it to row# 1741 of the destination sheet.

The problem:

The data contains more than 2,000 rows of records. My current code is pretty slow when the function runs, iterating to every row and doing the copy and paste per row from source (trackersheet) to destination (mmics or bau sheet).

The destination setValue is not for adjacent rows or a series of rows like 1-100, 500-600, etc, but a random one like (1,3,8,54,798, etc).

What is the fastest way to do this?

My code:

function copyToDestination(){
/****   setting up external/destination sheets    ******/
    const toSyncDataRange   = trackerSheet.getRange(firstRowTrackerData,colToSync,dataRowsCount,1).getValues();
    const dataRowsCount    = lastRowTrackerData - firstRowTrackerData;
    const sourceSpreadSheet = SpreadsheetApp.openById(DataSourceSSId);  
    const BAUSheetName      = sheet.getRange("BAUSheetName").getValue();
    const MMICSSheetName    = sheet.getRange("MMICSSheetName").getValue();
    const mmicsSheet        = sourceSpreadSheet.getSheetByName(MMICSSheetName);
    const bauSheet          = sourceSpreadSheet.getSheetByName(BAUSheetName);


    //trackerSheet is the source sheet

    let toUpdate = false;
    //reiterate through the ToSync Column to check for TRUE values
    for (let i = 0; i < dataRowsCount; i++) {   //this can still be optimized
        if(toSyncDataRange[i][0] === true){     //if toSync is checked or TRUE
            let currentRow = i + 1 + TrackerHeaderRow; //+1 coz index starts at 0; trackerheaderrow is the header row
            
            //this is the data range to be copied to destination
            const editableData      = trackerSheet.getRange(currentRow,colFirstEditableTracker,1,numColsTrackerData).getValues();

            //get rowId within source sheet
            const rowId     = trackerSheet.getRange(currentRow,colRowId).getValue();
            const source    = trackerSheet.getRange(currentRow,colSourceTracker).getValue(); 
            
            //if source at column B is ICS, then use the MMICS sheet; else use BAU sheet
            if(source === "ICS"){
              const mmics = mmicsSheet.getRange(rowId,1,1,numColsTrackerData);//.setValues(editableData);
              mmics.setDataValidation(null);
              mmics.setValues(editableData); //this can be optimized
            }
            else if(source === "BAU"){
              const bau = bauSheet.getRange(rowId,1,1,numColsTrackerData);//.setValues(editableData);
              bau.setDataValidation(null);
              bau.setValues(editableData); //this can be optimized

            }                   

            trackerSheet.getRange(currentRow,colLastUpdated,1,1).setValue("UPLOADED");  //this can be optimized
            toUpdate = true;      
        }
    }

    if(toUpdate){             
        SpreadsheetApp.getActive().toast("Finished uploading changes to original source...");
    }
}

Answer 1

Using batchUpdate method of Sheets API v4 is the solution to my problem:

• Make sure that the major dimension is ROWS if your unique destination reference is the row id/index, else it wont be read properly by the Sheets api.
• Setup the values into a 2D array.
• The data source arrays must also be of the same length when comparing status flags (true/false).

Result:

• Uploading thousands of rows to an external sheet with a discontinuous series of ranges is quicker by leaps and bounds than the original code in my post.

Code below:

  const colSourceTracker    = trackerSheet.getRange("SourceColumn").getColumn();
  const sourceDataRange     = trackerSheet.getRange(firstRowTrackerData,colSourceTracker,dataRowsCount,1).getValues().flat(); 
  const rowIdDataRange      = trackerSheet.getRange(firstRowTrackerData,colRowId, dataRowsCount, 1).getValues().flat();   

  const lastColLetter    = trackerSheet.getRange("LastColLetter").getValue();
  const uploadData       = trackerSheet.getRange(firstRowTrackerData,colFirstEditableTracker,dataRowsCount,numColsTrackerData).getValues(); 

  let lastUpdatedDataRange  = trackerSheet.getRange(firstRowTrackerData,colLastUpdated,dataRowsCount,1); 
  let lastUpdatedData       = lastUpdatedDataRange.getValues(); 

  trackerSheet.getRange(currentRow,colLastUpdated,1,1).setValue("UPLOADED"); 
   
  const dataToUpload = new Array();

  for (let i = 0; i < dataRowsCount; i++) {
    if(flattenedToSync[i] === true){
      //https://stackguides.com/questions/63163972/google-sheet-api-values-batchupdate-limit-of-number-of-ranges-in-body  
      //https://stackguides.com/questions/45588514/google-spreadsheet-editor-update-sparse-cells

      if (sourceDataRange[i] === "ICS"){
        destSheet = MMICSSheetName;
      }
      else if(sourceDataRange[i] === "BAU"){
        destSheet = BAUSheetName;
      }            
      
      dataToUpload.push({range: destSheet+`!A${rowIdDataRange[i]}:${lastColLetter}${rowIdDataRange[i]}`,
                         majorDimension: 'ROWS', 
                         values: [uploadData[i]]
      });

      lastUpdatedData[i][0] = "UPLOADED";
      toUpdate = true;
    }
  }

  Sheets.Spreadsheets.Values.batchUpdate({data: dataToUpload, valueInputOption: "USER_ENTERED"}, DataSourceSSId);
  lastUpdatedDataRange.setValues(lastUpdatedData);
  
  SpreadsheetApp.getActive().toast("Finished upload.");

Answer 2

Whenever you can, use batch operations instead of updating spreadsheet cells in a loop. Check https://developers.google.com/apps-script/guides/support/best-practices#use_batch_operations