I'm currently working on a project that cross validate 2 sheets with approx 500 loops.

ROSTER

REGISTRATION

RESULT

Sample Data Sheet my problem is that my script sometimes getting run-time timeout. I am seeing map function but I'm not sure if it's applicable for my current problem.

here are my sheets that is included in my script. sheets are roster, form responses 1, reference, result. roster and registration tab have common columns (First name, Last Name, Date of Birth)

var SS              = SpreadsheetApp.getActiveSpreadsheet()
var rosterTab       = SS.getSheetByName('Roster')
var registrationTab = SS.getSheetByName('Responses 1')
var referenceTab    = SS.getSheetByName('Reference')
var resultTab       = SS.getSheetByName('Result') //this is where I posts all registered names that is on the roster tab.
var xLastRow        = registrationTab.getLastRow();
var yLastRow        = rosterTab.getLastRow();
var Data            = [];
var codeStart       = referenceTab.getRange('A1').getValue();
var rosterdata      = rosterTab.getRange(1,1,300,3).getValues();
var registerdata    = registrationTab.getRange(1,1,xLastRow,3).getValues();

for(var i = 0; i<yLastRow; i  (){
    for(var j = 0; j<xLastRow; j  (){
        if (rosterdata[i][0] === registerdata[j][0] && rosterdata[i][1] === registerdata[j][1] && rosterdata[i][2] === registerdata[j][2]){
            var rosterFname = registerdata[j][0];
            var rosterLname = registerdata[j][1];
            var rosterDoB   = registerdata[j][2];
            var rosterCode  = codeStart;

            Data.push([rosterFname,rosterLname,rosterDoB,rosterCode]);

            break;
        }
    }
}
resultTab.getRange( resultTab.getLastRow() 1, 1, Data.length,Data[0].length ).setValues(Data);

Is there another way on how to approach this matching without getting timed-out?

CodePudding user response：

function myfunky11() {
  const ss = SpreadsheetApp.getActive();
  const rosterTab = ss.getSheetByName('Roster');
  const registrationTab = ss.getSheetByName('Responses 1');
  const referenceTab = ss.getSheetByName('Reference');
  let rsh = ss.getSheetByName('Result'); 
  var data = [];
  const codeStart = referenceTab.getRange('A1').getValue();
  const ros = rosterTab.getRange(2, 1, rosterTab.getLastRow() - 1, 3).getDisplayValues().map(r => r[0]   r[1]   r[2]);
  const reg = registrationTab.getRange(2, 1, registrationTab.getLastRow() - 1, 3).getValues();
  reg.forEach(r => {
    let s = r[0].toString()   r[1].toString()   Utilities.formatDate(new Date(r[2]), Session.getScriptTimeZone(), "M/d/yyyy");
    if (ros.indexOf(s)>-1) {
      r.push(codeStart);
      data.push(r)
    }
  });
  rsh.clear();
  rsh.getRange(1, 1, data.length, data[0].length).setValues(data);
}

This works now

CodePudding user response：

You could process all the "Roster" data in batches by keeping track of the last row that you have processed with PropertiesService

const LAST_PROCESSED_ROW_PROPERTY_KEY = 'lastProcessedRow';
const PROCESS_ROW_PER_BATCH           = 100;

var scriptProperties = PropertiesService.getScriptProperties();
var lastProcessedRow = scriptProperties.getProperty( LAST_PROCESSED_ROW_PROPERTY_KEY );

if ( lastProcessedRow === null ) {
    lastProcessedRow = 0;
}

// Your variables here

var yLastRow          = rosterTab.getLastRow();
var rowsToBeProcessed = PROCESS_ROW_PER_BATCH;

if ( lastProcessedRow == yLastRow ) {
    Logger.log( 'No new data to be processed' );

    return;
}

if ( yLastRow - lastProcessedRow < PROCESS_ROW_PER_BATCH ) {
    rowsToBeProcessed = yLastRow - lastProcessedRow;
}

var rosterdata = rosterTab.getRange( lastProcessedRow   1, 1, rowsToBeProcessed, 3 ).getValues();

for( var i = 0; i < rosterdata.length; i   ) {
    // Your data processing logic here
}

// Set data logic here

scriptProperties.setProperty( LAST_PROCESSED_ROW_PROPERTY_KEY, lastProcessedRow   rowsToBeProcessed );

CodePudding user response：

I believe your goal is as follows.

• There are 3 sheets in your Spreadsheet.
• You want to retrieve the values from "Form Response 1" and "Roster" sheets. When the values from the columns "B" to "D" in "Form Response 1" sheet are the same as the columns "A" to "C" in "Roster" sheet, you want to put them to "Result" sheet as the append values.
• You want to reduce the process cost of this process.

In this case, how about the following modified script?

Modified script:

function myFunction() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var rosterTab = SS.getSheetByName('Roster');
  var registrationTab = SS.getSheetByName('Form Response 1');
  var referenceTab = SS.getSheetByName('Reference');
  var resultTab = SS.getSheetByName('Result');
  var xLastRow = registrationTab.getLastRow();
  var yLastRow = rosterTab.getLastRow();
  var Data = [];
  var codeStart = referenceTab.getRange('A1').getValue();
  var rosterdata = rosterTab.getRange(1, 1, yLastRow, 3).getValues();
  var registerdata = registrationTab.getRange(2, 2, xLastRow - 1, 3).getValues();

  // I modified below script.
  // Create an object.
  var obj = rosterdata.reduce((o, [a,b,c]) => (o[a   b   c] = true, o), {});

  // Create an array for putting to sheet.
  var values = registerdata.reduce((ar, [a, b, c]) => {
    if (obj[a   b   c]) ar.push([a, b, c, codeStart]);
    return ar;
  }, []);
  
  // Put the array to the result sheet.
  if (values.length == 0) return;
  resultTab.getRange(resultTab.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}

• In this modification, create an object for searching the values and create an array for putting to the sheet, and the created array is appended to the result sheet. By this flow, I thought that the process cost might be able to be reduced a little.

Reference:

• reduce()