Improve performance in Google App Script Search functions

Question

I have written a script using Google App Script (in javascript) and I am looking for a way to be best optimize function that return objects, based on one or more search fields. The data is stored in a Google Sheet. The UI I have passes parameters to my function, then I iterate over a given sheet to find rows that meet a criteria, and add cells to an object, to be returned. The return could be just one object or a list of objects. For the most part, this works fine, but if I have this type of function nested in a loop it can really drag the performance. Any advise on how to improve performance would be greatly appreciated. Here is an example of my code:

function GetAllReportByOrgID_DataLayer_(org_id, reporting_periods) {
    //get all reporting period for program
    var rows = GetDataRows_(DATA_SPREAD_SHEET_ID, RESPONSE_PAGE);
    var surveys = [];   
    for (var i = 1; i < rows.length; i++) {
        var row = rows[i];
        var found_org_id = row[2];
        var found_is_active = row[13];
        if (found_org_id == org_id && found_is_active == true ) {
            var survey = {};
            survey.indicator_id = row[0];
            survey.program_id = row[1];
            survey.org_guid = row[2];
            survey.survey_response = row[3];
            survey.reporting_period = row[5];
            survey.reporting_period_name = GetReportingPeriodNameById_(row[5], reporting_periods);
            survey.is_final_report = row[6];
            survey.is_submitted = row[7];
            survey.submitted_by = row[8];
            survey.submitted_by_email = row[9];
            survey.date_created = ConvertUnixTimeStampToDateTime_(row[10]);
            survey.date_updated = ConvertUnixTimeStampToDateTime_(row[11]);
            survey.fiscal_year = row[12];
            survey.documents = GetDocumentsById_DataLayer_({
                 program_id: row[13]
            });
            surveys.push(survey);
        }
    }
    surveys.success = true;
    return surveys;
}
function GetDataRows_(Sheet_Id, SheetName) {
    var sheet = GetSheet_(Sheet_Id, SheetName);
    var rows = [];
    if (sheet) {
        rows = sheet.getDataRange().getValues();
    }
    return rows;
}
function GetSheet_(Sheet_Id, SheetName) {  
  var ss = SpreadsheetApp.openById(Sheet_Id);
  var sheet = ss.getSheetByName(SheetName);
  return sheet;
}
function GetReportingPeriodNameById_(id, reporting_periods) {
   if (id) {
       for (var i = 0; i < reporting_periods.length; i++) {
           if (reporting_periods[i].id == id) {
              return reporting_periods[i].value
           }
        }
      return "Reporting Period Not Found"
  } else {
    return "Reporting Period Not Found"
 }
}

function GetDocumentsById_DataLayer_(data) {
    var rows = GetDataRows_(DATA_SPREAD_SHEET_ID, PROGAM_DOCUMENTS_PAGE);
    var documents = [];
    var program_id = data.program_id.trim();

    for (var i = 1; i < rows.length; i++) {
        var row = rows[i];
        var found_program_id = row[1];
        var is_active = row[6];
    if(is_active === true){
      if (found_program_id === program_id) {
        var document = {};
        document.document_id = row[0];
        document.program_id = row[1];
        document.document_name = row[2];
        document.file_id = row[3];
        document.file_name = row[4];
        document.file_url = row[5]
        document.date_created = ConvertUnixTimeStampToDateTime_(row[7]);
        document.date_updated = ConvertUnixTimeStampToDateTime_(row[8]);
        documents.push(document);
      }
    }       
    }
    documents.success = true;
    return documents;
}

function ConvertUnixTimeStampToDateTime_(unix_timestamp) {
    if (!unix_timestamp) {
        return "";
    }
    var a = new Date(unix_timestamp * 1000);
    var months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
    var year = a.getFullYear();
    var month = months[a.getMonth()];
    var date = a.getDate();
    var hour = a.getHours();
    var min = a.getMinutes();
    var sec = a.getSeconds();
    var time = a.getMonth() + "/" + date + "/" + year + " " + hour + ":" + min + ":" + sec;
    return time;
}

This code mostly works fine, except when it is in a loop that gets called 100 times or so, then things lag and can take a minute or more to process. The sheets aren't that big, less that 200 rows and 15 columns.
thanks

Answer 1

You can refer to this sample code:

Data_Layer.gs

function GetAllDataByManager_DataLayer_(loggedin_user) {
  var ss = SpreadsheetApp.openById(DATA_SPREAD_SHEET_ID);

  var sheets = ss.getSheets();
  // create sheet objects
  var sheetData = {};
  sheets.forEach(sheet => {
    sheetData[sheet.getName()] = sheet.getDataRange().getValues();
  })

    var program_rows = sheetData[PROGRAM_MANAGERS];
    var ip_ids = [];
    var Company_data = [];
  var runtimeCountStart = new Date();

  //first search to make sure logged in user exists, and is marked as Active == True and is marked as an Admin
  //if they exist, add their ID to the list 
  //Should only see records for the logged in user if
  //they are either listed as the primary manager  (company tab)
  //or they are listed as an additional manager (program managers tab)

    for (var i = 1; i < program_rows.length; i++) {
        var row = program_rows[i];
        var found_admin_email = row[2];
        var found_is_active = row[10];
        if (found_admin_email == loggedin_user && found_is_active == true) {
            ip_ids.push(row[1])
        }
    }

  var partner_rows = sheetData[PARTNER_PAGE];  
  for (var i = 1; i < partner_rows.length; i++) {
    var partner_row = partner_rows[i]
    var found_partner_id = partner_row[0];
    var add_record = false;
    if(ip_ids.includes(found_partner_id)){
      add_record = true;
    }else{
      var found_cor_email = partner_row[5]
      if(loggedin_user.toUpperCase() == found_cor_email.toUpperCase()){
        add_record = true;
      }
    }    
    if(add_record == true){
      var partner = {
                    ip_id: partner_row[0],
                    ip_name: partner_row[1],
                    ip_poc_name: partner_row[2],
                    ip_poc_email: partner_row[3],
                    manager_name: partner_row[4],
                    manager_email: partner_row[5],
                    is_active: partner_row[6],
                    date_created: partner_row[7],
                    created_by: partner_row[8],
                    partner_programs:  GetAllProgramDataByIP_DataLayer_(sheetData, found_partner_id),  
          partner_notes: GetProgramNarrativesByPartnerID_DataLayer_(sheetData, found_partner_id),
          partner_reports: GetAllReportByPartnerID_DataLayer_(sheetData, found_partner_id)       
                };
                Company_data.push(partner)
    }

  }

  stop = new Date();
  newRuntime = Number(stop) - Number(runtimeCountStart);
  newRuntime  = (newRuntime /1000)/60

    return Company_data;

}
function GetAllProgramDataByIP_DataLayer_(sheetData, ip_id) {
    var rows = sheetData[PROGRAM_PAGE];
    var programs = [];
    for (var i = 1; i < rows.length; i++) {
        var row = rows[i];
        var found_partner_id = row[1];
        var program = {}
        if (found_partner_id === ip_id) {      
            program.program_id = row[0].toString();
            program.partner_id = row[1].toString();
            program.program_name = row[2].toString();
            program.program_country = row[3].toString();        
            program.program_background = row[4].toString();
            program.program_objectives = row[5].toString();
            program.program_justification = row[6].toString();
            program.program_start_date = row[7].toString();
      program.program_status = row[8].toString();
            program.program_contract_number = row[9].toString();            
            program.is_active = row[10]
            program.date_created = ConvertUnixTimeStampToDateTime_(row[11].toString());
            program.date_updated = ConvertUnixTimeStampToDateTime_(row[12].toString());
            program.success = true;
            programs.push(program)
        }

    }
    return programs;
}
function GetProgramNarrativesByPartnerID_DataLayer_(sheetData, partner_id) {
    var rows = sheetData[PROGRAM_NARRATIVE_NOTE];
    var programs_notes = [];
    var response = {};
    for (var i = 1; i < rows.length; i++) {
        var row = rows[i];
        var found_partner_id = row[2];
        var is_active = row[7];
    if(is_active === true){
      if (found_partner_id === partner_id) {
        var note = {};
        note.note_id = row[0];
        note.program_id = row[1];
        note.company_guid = row[2];        
        note.note_title = htmlEscape_(row[3]);
        note.note_desc = htmlEscape_(row[4]);
        note.note_reportingPeriod = row[5];
        note.activity_theme = row[6];
        note.date_created = ConvertUnixTimeStampToDateTime_(row[8]);
        note.date_updated = ConvertUnixTimeStampToDateTime_(row[9]);
        programs_notes.push(note);
      }
    }       
    }
    response.success = true;
    response.programs_notes = programs_notes
    return response;
}
function GetAllReportByPartnerID_DataLayer_(sheetData, partner_id) {
    //get all reporting period for program
    var rows = sheetData[RESPONSE_PAGE];  
    var surveys = [];   
    for (var i = 1; i < rows.length; i++) {
        var row = rows[i];
        var found_partner_id = row[2];
        if (found_partner_id == partner_id) {
            var survey = {};
            survey.indicator_id = row[0];
            survey.program_id = row[1];
            survey.company_guid = row[2];
            survey.survey_response = row[3];
            survey.reporting_period = row[5];
            survey.is_final_report = row[6];
            survey.is_submitted = row[7];
            survey.submitted_by = row[8];
            survey.submitted_by_email = row[9];
            survey.date_created = ConvertUnixTimeStampToDateTime_(row[10]);
            survey.date_updated = ConvertUnixTimeStampToDateTime_(row[11]);
            survey.fiscal_year = row[12];
            surveys.push(survey);
        }
    }
    surveys.success = true;
    return surveys;
}

Helper_Functions.gs

function ConvertUnixTimeStampToDateTime_(unix_timestamp) {
    if (!unix_timestamp) {
        return "";
    }
    var a = new Date(unix_timestamp * 1000);
    var months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
    var year = a.getFullYear();
    var month = months[a.getMonth()];
    var date = a.getDate();
    var hour = a.getHours();
    var min = a.getMinutes();
    var sec = a.getSeconds();
    var time = a.getMonth() + "/" + date + "/" + year + " " + hour + ":" + min + ":" + sec;
    return time;
}

function CreateGUID_() {
    return Utilities.getUuid();
}
function htmlEscape_(str) {
  str = str.toString();
    if (str && str.length > 0) {
        return str.replace(/<[^>]+>/g, "")
    } else {
        return "";
    }
}

Note:

• I didn't see GetDocumentsById_DataLayer_ and survey.documents in the shared script.
• I just looked for something to improve in your main function GetAllDataByManager_DataLayer_() where nested loop occur when you are checking your partner_rows variable.
• I don't think there's any more we can do regarding your nested loop check.

Modifications Done:

• Although you prevented repeating the calls for Spreadsheet.openById(), you still make individual calls when you map your sheet objects since you use ss.getSheetByName(SheetName); to get individual sheet object based on the SheetName and even reading the sheet values during every loop for 3 different sheets
• What I did is to get all your sheet object available in your spreadsheet using getSheets(), get its data range values then map them based on their sheet name. As what was suggested by @TheMaster in the other answer
• Remove unnecessary functions in Helper_Functions.gs

Execution Log:

• For the code changes, refer to the last 2 recent Web Apps execution logs. Compare it to the first execution

Answer 2

The reason for performance depreciation is the GetDataRows_ function, because it makes repeated calls to Spreadsheet.openById(), ss.getSheetByName(SheetName);. You could try using a global map to cache these objects. For example, Use a Spreadsheet map

const globalSSMap = new Map();//global SS map

Then your getter functions can be rewritten. For eg, to get ss, use

if(!globalSSMap.has(Sheet_id)) globalSSMap.set(Sheet_id,SpreadsheetApp.openById(Sheet_Id));
var ss = globalSSMap.get(Sheet_id);