Timeout error while running script to get ames with address, phone number, email, and job titles

Question

I have over 500 contacts in Google. I am wanting to list all the contacts that are associated with a particular company into a sheet. When I run the following script, I receive a timeout error.

    function Step2_Contacts(x){
  //Adding Contact information
  var x = "Company Name";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(x);
  var contacts, outputTitle = [], outputEmail = [], outputPhone = [], outputAddress = [], outputName = [], outputNotes = [], cStart = ContactsApp.getContacts();  
  for(var i = 0, iLen = cStart.length; i < iLen; i++) {
    var fullname = cStart[i].getFullName();  
    contacts = ContactsApp.getContactsByName(fullname);
    for (y in contacts) {
      var company = contacts[y].getCompanies();
      var email = contacts[y].getEmails(ContactsApp.Field.WORK_EMAIL);
      var phone = contacts[y].getPhones();
      var addr = contacts[y].getAddresses(ContactsApp.Field.HOME_ADDRESS);
      var notes = contacts[y].getNotes();
      for (var z in state){
        if (company[y].getCompanyName() == x){
          outputTitle.push([company[z].getJobTitle()]);
          outputEmail.push([email[z].getAddress()]);
          outputPhone.push([phone[z].getPhoneNumber()]);
          outputAddress.push([addr[z].getAddress()]);
          outputName.push([fullname]);
          outputNotes.push([notes]);
        };
      };
    };
  };

  //Adding in Contact Information
  sheet.getRange(7, 4, outputName.length, 1).setValues(outputName);
  sheet.getRange(7, 5, outputTitle.length, 1).setValues(outputTitle);
  sheet.getRange(7, 6, outputEmail.length, 1).setValues(outputEmail);
  sheet.getRange(7, 7, outputPhone.length, 1).setValues(outputPhone);
  sheet.getRange(7, 8, outputAddress.length, 1).setValues(outputAddress);
  sheet.getRange(7, 9, outputNotes.length, 1).setValues(outputNotes);
};

What I think happens is that every loop the arrays will add another dimension to all the arrays listed within the loop.

Answer 1

Google Apps Script has execution time limits that vary according the type of account you are using. For details see https://developers.google.com/apps-script/guides/services/quotas

One solution is to reduce of the execution time required by your script by reducing the calls to the Spreadsheet Service (SpreadsheetApp). To do this, instead of adding the contact values to the spreadsheet on your for loop, create a 2D array, then pass all the values at once with a single setValues(myArray)

Answer 2

I rearranged your code a little. See if this will work for you. I don't know what cStart and state are...Presumably global variables that I can't see.

function Step2_Contacts(x){
  //Adding Contact information
  var x = "Company Name";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(x);
  var contacts, outputTitle = [], outputEmail = [], outputPhone = [], outputAddress = [], outputName = [], outputNotes = [], cStart = ContactsApp.getContacts();  
  for(var i = 0, iLen = cStart.length; i < iLen; i++) {
    var fullname = cStart[i].getFullName();  
    contacts = ContactsApp.getContactsByName(fullname);
    for (y in contacts) {//PROBLEM: INDEXING THROUGH contacts AND company WITH SAME KEY
      var company = contacts[y].getCompanies();
      //PROBLEM: INDEXING THROUGH contacts AND company WITH SAME KEY
      //I JUST NOTICED THAT SO DON'T TRY THIS CODE
      if (company[y].getCompanyName() == x){//if you can do this earlier in your code you can avoid doing a lot of useless stuff since your only looking for one company name
        var email = contacts[y].getEmails(ContactsApp.Field.WORK_EMAIL);
        var phone = contacts[y].getPhones();
        var addr = contacts[y].getAddresses(ContactsApp.Field.HOME_ADDRESS);
        var notes = contacts[y].getNotes();
        for (var z in state){
          outputTitle.push([company[z].getJobTitle()]);
          outputEmail.push([email[z].getAddress()]);
          outputPhone.push([phone[z].getPhoneNumber()]);
          outputAddress.push([addr[z].getAddress()]);
          outputName.push([fullname]);
          outputNotes.push([notes]);
        };
        break;
      };
    };
  };

  //Adding in Contact Information
  sheet.getRange(7, 4, outputName.length, 1).setValues(outputName);
  sheet.getRange(7, 5, outputTitle.length, 1).setValues(outputTitle);
  sheet.getRange(7, 6, outputEmail.length, 1).setValues(outputEmail);
  sheet.getRange(7, 7, outputPhone.length, 1).setValues(outputPhone);
  sheet.getRange(7, 8, outputAddress.length, 1).setValues(outputAddress);
  sheet.getRange(7, 9, outputNotes.length, 1).setValues(outputNotes);
};

Answer 3

Thank you Rubén for giving me the right idea to go on.

I have solved my problem with the following code.

function templateStep2(x){
  //Adding Contact information
  var x = "Company Name";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(x);
  var allContacts = [], cStart = ContactsApp.getContacts(); 
  var companyName = "", fullname = "", jobTitle = "", email = "", phone = "", addr = "", notes = "";
  var companyContacts = [];
  var test = "";
  var a = 0;
  for(var i = 0, iLen = cStart.length; i < iLen; i++) {
    fullname = cStart[i].getFullName();  
    var pass = cStart[i].getCompanies();
    companyName = pass[0].getCompanyName();
    jobTitle = pass[0].getJobTitle();
    pass = cStart[i].getEmails();
    email = pass[0].getAddress();
    pass = cStart[i].getPhones();
    phone = pass[0].getPhoneNumber();
    pass = cStart[i].getAddresses();
    addr = pass[0].getAddress();
    notes = cStart[i].getNotes();
    allContacts[i] = [companyName,fullname,jobTitle,email,phone,addr,notes];
  };
  for (var i = 0, iLen = cStart.length; i < iLen; i++) {
    test = allContacts[i][0];
    if (test == x){
      companyContacts[a] = allContacts[i];
      companyContacts[a].shift();
      a++
    };
  };
  //Adding in Contact Information
  sheet.getRange(7, 4, companyContacts.length, 6).setValues(companyContacts);
};

The variable cStart is set as the call to the Contacts app. It will contain the number of how many Contacts are listed in Google.

I then use 'pass' to pass though the correct group. Such as phones, companies, addresses, and the like. Because each contact is listed in the array by the numeric value, we use the counting variable 'i' to locate the correct contact pass = cStart[i].getCompanies. When the 'pass' variable has been established, the first entry in the contact will contain the information I am seeking companyName = pass[0].getCompanyName().

This is all collected into an array. That will allow me to only access the Google Application one time instead of creating a loop that will access the Application n number of times.

From there I just run the array through an IF filter and remove the excess information.