1

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.

  • What is cStart ? – Cooper Feb 16 at 21:58
  • What is state? – Cooper Feb 16 at 22:09
  • Your using y to index through contacts and company at the same time. I think that's a problem. – Cooper Feb 16 at 22:37
  • You are probably correct. I worked out my answer. I am new to coding so my apologies if my understanding is quite elementary. – Government Relations Feb 18 at 19:33
0

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)

  • 1
    I would have upvoted your answer as it really helped me find the answer. Due to my low reputation score, I cannot. – Government Relations Feb 18 at 19:21
0

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.