How to improve for loop without getting runtime timeout,

Question

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

ROSTER

First Name	Last Name	DoB
Judith	Barragan	4/10/1959
Kelly	Benitez	9/14/1993
Martha	Bustos	1/12/1960
Robyn	Carroll	5/9/1954
Janet	Chambers	8/27/1949
Nikki	Corso	10/25/1957
Angella	Decohen	5/23/1988
Damian	Delaney	6/26/1961
Anora	Denison	4/14/1998
Cristina	Dimatulac	7/28/1959
Mercy	Erazo	3/14/1959
Michelle	Fanara	11/20/1981
Shannon	Feldmann	9/10/1986
Alejandra	Frutos-Silva	2/14/1978
Rebecka	Aceves	7/14/2007
Jarely	Aguilera	6/8/2006
Jasmine	Aguillon	1/29/2007
Adriana	Alaniz	10/4/2007
Blanca	Angel	11/3/2007
Francie	Arellano	9/11/2007
Molly	Barajas	10/1/2007
Emily	Barranco	9/12/2007
Valeria	Bata	3/29/2007
Sarahi	Cabeza	8/8/2007
Carla	Cadena	3/31/2006
Emily	Cano	1/25/2007
Janet	Canul	4/27/2007
Caitlyn	Castaneda	3/26/2007
Jacqueline	Castillo	1/22/2007
Melanie	Colindres	6/8/2007
Nyah	Davis	8/8/2007
Karie	Delgadillo	2/10/2007
Gabriela	Diaz	6/25/2007
Helen	Diaz	8/17/2007
Hailey	Duran	5/20/2007
Hazel	Flores	9/7/2007
Kiherra	Gamboa	10/4/2007
Belen	Gonzalez	4/23/2007
Samantha	Gonzalez	10/16/2007
Ashlee	Palacios	8/31/2006
Naomi	Papaqui	5/17/2007
Karely	Paxtor	10/21/2006
Michelle	Paxtor	10/20/2007
Audra	Perez	2/24/2007
Josueline	Perez	10/30/2006
Yaretzi	Pineda	2/17/2007
Zuleyka	Portela	9/10/2007
Jacqueline	Prudencio	3/1/2007
Destiny	Quiroz	5/10/2007
Kelcey	Raiz	5/11/2007
Brianna	Ramos	8/15/2007
Neydy	Renderos	8/26/2007
Daiman	Johnson	3/3/1968
Kimberley	Rivas	11/2/2007
Michelle	Dominguez	5/15/2005
Marleny	Rodriguez	7/29/2007
Maria	Roman	5/9/2006
Cristal	Solis	9/29/2006
Carmela	Torralba	6/28/2007
Dora	Vasquez	5/14/2007
Cindy	Vega	11/20/2007
Jennifer	Velasco	6/30/2006
Chloe	Wilson	10/8/2007
Melody	Zacarias	5/17/2007
Hazel	Zamora	1/27/2007
Kayden	Alexander	2/6/2006
Yvette	Alvarado	3/30/2006
Damian	Delaney	6/26/1961
Kimberly	Amezcua	7/14/2006
Kimberly	Antonio	5/30/2006
Alicia	Aquino	6/15/2006
Samantha	Aquino	6/27/2006
Destiny	Arauz	6/13/2006
Julissa	Arroyo	5/26/2006
Cassandra	Ayala	8/18/2006
Samantha	Ayala	7/2/2006
Eva	Azul	2/6/2006
Stacey	Bacelis	4/4/2006
America	Baires	7/17/2006
Ashley	Barajas	6/10/2005
Janet	Barrera	10/14/2005
Alisa	Benitez	5/26/2006
Sara	Bolanos-Mejia	1/12/2006
Ashley	Mendez	6/22/2006
Ana	Carvente	7/12/2006
Mia	Castellanos	6/19/2006
Rosalma	Cebreros	3/3/2006
Yosselin	Celis	5/25/2005
Jacqueline	Lucero	9/2/1974
Evelyn	Chamu	1/30/2006
Nataly	Chavez	2/27/2006
Juliana	Coeto	10/4/2005
Shesith	Covarrubias	12/8/2005
Ashley	Cruz	7/20/2006
Erin	Dakers	8/2/2007
Claudia	Lopez	6/16/2007
Cristina	Diaz	10/13/2005
Zoe	Dighero	4/11/2006
Kaylynn	Domingo	10/4/2006
Celeste	Dominguez	6/1/2006
Lizzy	Escobar	12/14/2005
Lilian	Escorza	12/23/2005

REGISTRATION

First Name	Last Name	DoB
Jacqueline	Lucero	9/2/1974
Ashley	Mendez	6/22/2006
Hyobe	Namkoong	6/19/2007
Hetzabel	Sanchez	4/13/2005
cristal	solis	9/29/2006
Briseida	Lopez	5/22/2005
Daiman	Johnson	3/3/1968
Kayleen	Vasquez	12/9/2003
Ashley	Aguilar	12/9/2003
Damian	Delaney	6/26/1961
Michelle	Dominguez	5/15/2005
Martha	Bustos	1/12/1960
Jaqueline	Granadino	9/6/2004
jacqueline	granadino	9/6/2004
Jacqueline	granadino	9/6/2004
Maria	Gutierrez	11/30/2006
Claudia	Lopez	6/16/2007
Kelly	Benitez	9/14/1993
Kelly	Benitez	9/14/1993

RESULT

First Name	Last Name	DoB	Start Code
Jacqueline	Lucero	9/2/1974	1980001
Ashley	Mendez	6/22/2006	1980002
Cristal	Solis	9/29/2006	1980003
Daiman	Johnson	3/3/1968	1980004
Damian	Delaney	6/26/1961	1980005
Michelle	Dominguez	5/15/2005	1980006
Martha	Bustos	1/12/1960	1980007
Claudia	Lopez	6/16/2007	1980008
Kelly	Benitez	9/14/1993	1980009

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?

Answer 1

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

Answer 2

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()

Answer 3

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