Add your signup sheet to Google Contacts

Once a day, the spreadsheet your signups or enquiries land in is checked against Google Contacts. Every row is matched to your address book by email address: somebody new is added as a contact, and somebody already there is left in place rather than entered twice.

Nothing already on a contact is ever overwritten. When a row matches an existing contact, only what is blank is added, such as a missing phone number or name, and if your sheet holds a different phone number from the card it says so in the run summary and changes nothing. A row with no first name, or nothing usable in the 'Email' column, is skipped and named at the end, because without an email there is no safe way to tell that person from someone already there. No contact is ever deleted, and an unchanged sheet writes nothing.

Your tab needs a header row with the columns 'First Name', 'Last Name', 'Email' and 'Phone'; last name and phone may be empty. Company names are not stored.

Run every day.Run every day.

Set signupSheetUrl to blank.Set signupSheetUrl to blank.

Set signupTabName to blank.Set signupTabName to blank.

Google SheetsRead the rows of signupSheetUrl. Save it as signupSheet.Read the rows of signupSheetUrl. Save it as signupSheet.

Set signupRows to signupSheet.rows.Set signupRows to signupSheet.rows.

Set signupRowCount to 0.Set signupRowCount to 0.

If a step below fails, save the error as rowCountErrorIf a step below fails, save the error as rowCountError

Try

Count the items in signupRows and save it as signupRowCount.Count the items in signupRows and save it as signupRowCount.

On error

Change signupRowCount to 0.Change signupRowCount to 0.

If signupRowCount is 0If signupRowCount is 0

Yes

Finish the run: There are no signups to read yet, so nothing was added to your contacts.Finish the run: There are no signups to read yet, so nothing was added to your contacts.

No

No steps

Google ContactsList contacts. Save it as addressBook.List contacts. Save it as addressBook.

Set knownContacts to addressBook.contacts.Set knownContacts to addressBook.contacts.

Set knownContactCount to 0.Set knownContactCount to 0.

If a step below fails, save the error as contactCountErrorIf a step below fails, save the error as contactCountError

Try

Count the items in knownContacts and save it as knownContactCount.Count the items in knownContacts and save it as knownContactCount.

On error

Change knownContactCount to 0.Change knownContactCount to 0.

If knownContactCount is 0If knownContactCount is 0

Yes

Start an empty list and save it as knownContacts.Start an empty list and save it as knownContacts.

No

No steps

Start an empty list and save it as knownEmails.Start an empty list and save it as knownEmails.

For each knownContact in knownContacts:For each knownContact in knownContacts:

Set knownEmail to blank.Set knownEmail to blank.

If a step below fails, save the error as contactEmailErrorIf a step below fails, save the error as contactEmailError

Try

Make knownContact.email lowercase and save it as knownEmail.Make knownContact.email lowercase and save it as knownEmail.

On error

Change knownEmail to blank.Change knownEmail to blank.

If knownEmail is emptyIf knownEmail is empty

Yes

Change knownEmail to (this contact has no email address).Change knownEmail to (this contact has no email address).

No

No steps

Add knownEmail to the end of knownEmails and save it as knownEmails.Add knownEmail to the end of knownEmails and save it as knownEmails.

Start an empty list and save it as addedPeople.Start an empty list and save it as addedPeople.

Start an empty list and save it as updatedPeople.Start an empty list and save it as updatedPeople.

Start an empty list and save it as needsAttention.Start an empty list and save it as needsAttention.

Start an empty list and save it as skippedRows.Start an empty list and save it as skippedRows.

Set alreadyCurrentCount to 0.Set alreadyCurrentCount to 0.

For each signupRow in signupRows:For each signupRow in signupRows:

Set firstName to blank.Set firstName to blank.

Set lastName to blank.Set lastName to blank.

Set signupEmail to blank.Set signupEmail to blank.

Set signupPhone to blank.Set signupPhone to blank.

If a step below fails, save the error as firstNameReadErrorIf a step below fails, save the error as firstNameReadError

Try

Trim signupRow["First Name"] and save it as firstName.Trim signupRow["First Name"] and save it as firstName.

On error

Change firstName to blank.Change firstName to blank.

If a step below fails, save the error as lastNameReadErrorIf a step below fails, save the error as lastNameReadError

Try

Trim signupRow["Last Name"] and save it as lastName.Trim signupRow["Last Name"] and save it as lastName.

On error

Change lastName to blank.Change lastName to blank.

If a step below fails, save the error as signupEmailReadErrorIf a step below fails, save the error as signupEmailReadError

Try

Trim signupRow.Email and save it as signupEmail.Trim signupRow.Email and save it as signupEmail.

On error

Change signupEmail to blank.Change signupEmail to blank.

If a step below fails, save the error as signupPhoneReadErrorIf a step below fails, save the error as signupPhoneReadError

Try

Trim signupRow.Phone and save it as signupPhone.Trim signupRow.Phone and save it as signupPhone.

On error

Change signupPhone to blank.Change signupPhone to blank.

Trim firstName lastName and save it as personName.Trim firstName lastName and save it as personName.

Set rowLabel to personName.Set rowLabel to personName.

If rowLabel is emptyIf rowLabel is empty

Yes

Change rowLabel to signupEmail.Change rowLabel to signupEmail.

No

No steps

If rowLabel is emptyIf rowLabel is empty

Yes

Change rowLabel to A row with no name and no email address.Change rowLabel to A row with no name and no email address.

No

No steps

If firstName is emptyIf firstName is empty

Yes

Add rowLabel - the 'First Name' column is empty, and a contact needs a first name. to the end of skippedRows and save it as skippedRows.Add rowLabel - the 'First Name' column is empty, and a contact needs a first name. to the end of skippedRows and save it as skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

If not signupEmail is a valid email addressIf not signupEmail is a valid email address

Yes

Add rowLabel - the 'Email' column is empty or is not an email address. Without one there is no way to tell whether they are already in your contacts, so this row was left alone. to the end of skippedRows and save it as skippedRows.Add rowLabel - the 'Email' column is empty or is not an email address. Without one there is no way to tell whether they are already in your contacts, so this row was left alone. to the end of skippedRows and save it as skippedRows.

Skip to the next item.Skip to the next item.

No

No steps

Make signupEmail lowercase and save it as matchKey.Make signupEmail lowercase and save it as matchKey.

Find the position of matchKey in knownEmails and save it as matchIndex.Find the position of matchKey in knownEmails and save it as matchIndex.

If matchIndex is less than 0If matchIndex is less than 0

Yes

Set createdResource to blank.Set createdResource to blank.

If a step below fails, save the error as createErrorIf a step below fails, save the error as createError

Try

Google ContactsCreate contact firstName. Save it as createResult.Create contact firstName. Save it as createResult.

Trim createResult.resource_name and save it as createdResource.Trim createResult.resource_name and save it as createdResource.

On error

Add rowLabel - could not be added to Google Contacts: createError as text to the end of skippedRows and save it as skippedRows.Add rowLabel - could not be added to Google Contacts: createError as text to the end of skippedRows and save it as skippedRows.

If createdResource isn't emptyIf createdResource isn't empty

Yes

Add rowLabel to the end of addedPeople and save it as addedPeople.Add rowLabel to the end of addedPeople and save it as addedPeople.

Add a record of resourceName, fullName, email, and phone to the end of knownContacts and save it as knownContacts.Add a record of resourceName, fullName, email, and phone to the end of knownContacts and save it as knownContacts.

Add matchKey to the end of knownEmails and save it as knownEmails.Add matchKey to the end of knownEmails and save it as knownEmails.

No

No steps

No

Set existingContact to knownContacts[@matchIndex].Set existingContact to knownContacts[@matchIndex].

Set existingResource to blank.Set existingResource to blank.

If a step below fails, save the error as resourceErrorIf a step below fails, save the error as resourceError

Try

Trim existingContact.resourceName and save it as existingResource.Trim existingContact.resourceName and save it as existingResource.

On error

Change existingResource to blank.Change existingResource to blank.

If existingResource is emptyIf existingResource is empty

Yes

Add rowLabel - matches a contact that came back without an ID, so it could not be updated. to the end of skippedRows and save it as skippedRows.Add rowLabel - matches a contact that came back without an ID, so it could not be updated. to the end of skippedRows and save it as skippedRows.

No

Set existingName to blank.Set existingName to blank.

If a step below fails, save the error as nameErrorIf a step below fails, save the error as nameError

Try

Trim existingContact.fullName and save it as existingName.Trim existingContact.fullName and save it as existingName.

On error

Change existingName to blank.Change existingName to blank.

Set existingPhone to blank.Set existingPhone to blank.

If a step below fails, save the error as phoneErrorIf a step below fails, save the error as phoneError

Try

Trim existingContact.phone and save it as existingPhone.Trim existingContact.phone and save it as existingPhone.

On error

Change existingPhone to blank.Change existingPhone to blank.

Set firstNameToAdd to blank.Set firstNameToAdd to blank.

Set lastNameToAdd to blank.Set lastNameToAdd to blank.

If existingName is emptyIf existingName is empty

Yes

Change firstNameToAdd to firstName.Change firstNameToAdd to firstName.

Change lastNameToAdd to lastName.Change lastNameToAdd to lastName.

No

No steps

Set phoneToAdd to blank.Set phoneToAdd to blank.

If signupPhone isn't emptyIf signupPhone isn't empty

Yes

If existingPhone is emptyIf existingPhone is empty

Yes

Change phoneToAdd to signupPhone.Change phoneToAdd to signupPhone.

Else if @existingPhone is not @signupPhone

Add rowLabel - your sheet says signupPhone but their contact card says existingPhone. The contact was left as it is; change it by hand if the sheet is right. to the end of needsAttention and save it as needsAttention.Add rowLabel - your sheet says signupPhone but their contact card says existingPhone. The contact was left as it is; change it by hand if the sheet is right. to the end of needsAttention and save it as needsAttention.

Otherwise

No steps

No

No steps

If firstNameToAdd isn't empty or phoneToAdd isn't emptyIf firstNameToAdd isn't empty or phoneToAdd isn't empty

Yes

If a step below fails, save the error as updateErrorIf a step below fails, save the error as updateError

Try

Google ContactsUpdate contact existingResource.Update contact existingResource.

Add rowLabel to the end of updatedPeople and save it as updatedPeople.Add rowLabel to the end of updatedPeople and save it as updatedPeople.

On error

Add rowLabel - is already in your contacts but could not be updated: updateError as text to the end of skippedRows and save it as skippedRows.Add rowLabel - is already in your contacts but could not be updated: updateError as text to the end of skippedRows and save it as skippedRows.

No

Add 1 to alreadyCurrentCount.Add 1 to alreadyCurrentCount.

Set runSummary to Signup sheet checked against Google Contacts.Set runSummary to Signup sheet checked against Google Contacts. Added: the length of addedPeople as text Updated: the length of updatedPeople as text Already up to date: alreadyCurrentCount as text Needs a look: the length of needsAttention as text Skipped: the length of skippedRows as text.

If addedPeople isn't emptyIf addedPeople isn't empty

Yes

Change runSummary to runSummaryChange runSummary to runSummary Added to your contacts: addedPeople joined by a new line.

No

No steps

If updatedPeople isn't emptyIf updatedPeople isn't empty

Yes

Change runSummary to runSummaryChange runSummary to runSummary Filled in on contacts you already had: updatedPeople joined by a new line.

No

No steps

If needsAttention isn't emptyIf needsAttention isn't empty

Yes

Change runSummary to runSummaryChange runSummary to runSummary Worth a look: needsAttention joined by a new line.

No

No steps

If skippedRows isn't emptyIf skippedRows isn't empty

Yes

Change runSummary to runSummaryChange runSummary to runSummary Skipped: skippedRows joined by a new line.

No

No steps

Show in the output: runSummary.Show in the output: runSummary.