CODEWITHSUNDEEP
google-apps-scriptrow
testing123
testing123

Reputation: 831

update google contact with marked sheet using google app scripts

I'm trying to update my Google contacts from a sheet. I want to search column I of the sheet to check for 'pending'. If a row is pending, I want to take the values in column G, H, and E of that row and add it to that contact's Note.

Here's what I've got so far. Currently stuck on how to get the row of a contact marked 'pending'.

function test(){ 
  var ss = SpreadsheetApp.getActiveSheet();
  var data = ss.getDataRange().getValues(); // read all data in the sheet
  for(n=9;n<data.length;++n){ // iterate row by row and examine data in column I
    if(data[n][0].toString().match('pending')){
       var row = ???
       var name = ss.getRange(row, 1).getValue();
       var contacts = ContactsApp.getContactsByName(name);
       for (var i in contacts) {
         var donate = ss.getRange(row, 7).getValue();
         var pickup = ss.getRange(row, 8).getValue();
         var item = ss.getRange(row, 5).getValue();
        contacts[i].setNotes(donate + '\n\n' + item + '\n\n' + pickup + '\n\n');}
     ss.getRange('9**row**').setValue('done');
    }
  }
}

Upvotes: 1

Views: 339

Answers (1)

Parag Jadhav
Parag Jadhav

Reputation: 1899

This should do, haven't tested it though. Let me know if the code does not work.

    function test()
    { 
       var ss = SpreadsheetApp.openById(<SPREADSHEET_ID>).getSheetByName("Sheet1");
       var lastRow = ss.getLastRow();
       var data = ss.getRange("I1:I"+lastRow).getValues(); // read all data in the sheet
       for(var n=0;n<data.length;n++)
       { // iterate row by row and examine data in column I
            if(data[n] == 'pending')
            {
               //var row = ???
               var name = ss.getRange("A"+n).getValue();
               var contacts = ContactsApp.getContactsByName(name);
               for (var i in contacts)
               {
                 var donate = ss.getRange("G"+n).getValue();
                 var pickup = ss.getRange("H"+n).getValue();
                 var item = ss.getRange("E"+n).getValue();
                contacts[i].setNotes(donate + '\n\n' + item + '\n\n' + pickup + '\n\n');}
             ss.getRange('9**row**').setValue('done');
            }
          }
        }

Instead of using var ss = SpreadsheetApp.getActiveSheet(); you should consider using var ss = SpreadsheetApp.openSheetById(<SPREADSHEET_ID>).getSheetByName(<SHEET_NAME>);

Upvotes: 1

Related Questions