Exceeded the maximum instruction count while exporting 600 contacts + groups

Question

I want to export my google contacts with their groups into a Google spreadsheet since the standard export file sucks. My dataset is not excessive (32 groups and 600 contacts). What am I doing wrong? How can I limit the number of instructions so I don't hit this error anymore?

---

function GetGroups() {

  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var blad6=ss.getSheets()[6];
  var vId;
  var vGroep;
  var lijn5=2;

  var contact = ContactsApp.getContacts();

  blad6.clearContents();
  blad6.getRange("A1").setValue("Naam");
  blad6.getRange("B1").setValue("Groep");
  blad6.getRange("C1").setValue("ID");

for (var i=0;i<contact.length;i++) {
    sleep(500);
    vId=contact[i].getId();
    vGroep=contact[i].getContactGroups();

    for (var j=0;j<vGroep.length;j++) {
      blad6.getRange("A" + (lijn5)).setValue(contact[i].getFullName());
      blad6.getRange("B" + (lijn5)).setValue(vGroep[j].getName());
      blad6.getRange("C" + (lijn5)).setValue(vId);
      lijn5++;
    }
  }
}

---

Thanks, that has resolved most of the problems. However, I'm still getting an Over Quota error on this section. How come? I'm constructing an array, just as you advized

function HaalGroepenOp() {

  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var blad6=ss.getSheets()[6];
  var resultaat=new Array();

  var vId;
  var vGroep;
  var lijn=1;

  var contact = ContactsApp.getContacts();

  blad6.clearContents();
  resultaat[0]=[["Naam"],["Groep"],["ID"]];

for (var i=0;i<contact.length;i++) {
    vId=contact[i].getId();
    vGroep=contact[i].getContactGroups();

    for (var j=0;j<vGroep.length;j++) {
      resultaat[lijn]=[[contact[i].getFullName()],[vGroep[j].getName()],[vId]];
      lijn++;
    }
  }
  blad6.getRange(1,1,lijn,3).setValues(resultaat);
}

Answer 1

Your are making too many calls to the spreadsheet with setValue for each individual item.

You should be building arrays and then assigning the array to the spreadsheet using setValues().

  blad6.getRange("A1").setValue("Naam");
  blad6.getRange("B1").setValue("Groep");
  blad6.getRange("C1").setValue("ID");

should be:

blad6.getRange("A1:C1").setValues([["Naam","Groep","ID"]]);

Likewise, you should build an array with your loop and then assign the array to the spreadsheet in one batch using setValues()

See Optimizing Scripts