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

CodePudding user response：

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

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