AppsScriptPulse

Snippet, Solutions, Contacts, Sheets

Adding data from spreadsheet to custom fields in Google Contacts with Google Apps Script

Tags:
| | 0 Comments |

At my work data of clients, opposing parties, courts, third parties and cases (among which case names) are stored in a spreadsheet. The spreadsheet is the source for contacts in Google Contacts.

For some reason names of cases are used as subject in Gmail. I use(d) Google Apps Script to add case names to Google Contacts

Nice little script example to navigate around and add data to Google Contacts.

Source: /**There must be a better way to do it but I do not yet know how*/: Adding data from spreadsheet to custom fields in Google Contacts with GAS

Snippet, Sheets

‘While’ loop with a ‘Sleep’ and ‘Toast’

Tags:
| | 0 Comments |

Screenshot of toast notification in Google Sheet
Screenshot of toast notification in Google Sheet

Understanding how to use a simple ‘while’ loop that pops up a ‘toast’ message every 5 seconds by making use of ‘Utilities.sleep’ to pause the script.

This was created as part of some testing for another script designed to bulk move files between Google Drive folders. Without the toast notification it was difficult to determine the progress being made, so adding it in allowed for the name of the file last copied to be displayed to the user. This script removes all of the extra features and shows the basic while loop and toast.

Source: The Gift of Script: While loop with a Sleep and Toast

Snippet, Sheets

Clear cell contents on Google Sheet edit

Tags:
| | 0 Comments |

Screenshot of coloured columns to highlight actions
Screenshot of coloured columns to highlight actions

This is designed to clear the adjacent cell to the right of the one that has just been edited – so long as it is within the column we specify.

It was developed as I was testing the available options for a Stock Inventory system where an item that was to be marked as back in stock would automatically clear an email timestamp in the adjacent cell (ready for the whole ordering process to start again).

Source: The Gift of Script: Clear cell content on Google Sheet edit

Snippet, Solutions, Drive, Sheets

Create alphabetised folders in Google Drive

Tags:
| | 0 Comments |

Screenshot of Google Drive folder layout

The script is designed to bulk create folders from a Google Sheet and to arrange them into alphabetised folders within Google Drive, by extracting the first letter of a user’s surname.

If a suitable alphabetised folder does not exist in Google Drive, then one is created. The script also adds the created folder to an ‘Everybody’ folder so that there is a single area with all the folders listed in.

Source: The Gift of Script: Create alphabetised folders in Google Drive

Snippet, Solutions, Drive, Sheets

Bulk rename files in Google Drive

Tags:
| |

Screenshot of File renamer text fields

Screenshot of File renamer text fields

This is designed to automate bulk renaming of multiple files within a single Google Drive folder. It has been developed as a Google Sheet with flexibility towards entering your own search pattern and replacement string as you see fit.

Source: The Gift of Script: Bulk rename files in Google Drive

Snippet, Solutions, Calendar

Search Google Calendar date range for specific events

Tags:
| | 0 Comments |

Search through a given date range on Google Calendar, look through the events and then extract their Title & Id.

Source: The Gift of Script: Search Google Calendar date range for specific events

Snippet, Solutions, Gmail, Sheets

Bulk send emails from a Google Sheet

Tags:
| |

This is designed to loop through each row of a Google Sheet and send an email containing information from it. This means you can send personalised emails with ad-hoc information for each recipient quickly.

It has been developed so that it is easily expandable for you to add further columns/rows. The script has a number of features to achieve this:

  1. A menu option for easily selecting to send email, with a confirmation dialogue popup.
  2. A timestamp as confirmation for when an email was sent.
  3. A check that an Email Address is present and there is no current value in the Email Sent column – to prevent repeat email sending but also allow ad-hoc emailing by clearing this column for those you wish to send again.
  4. A try/catch to help automatically deal with problematic emails that may break the script – an error message will be written into the Email Sent column and the script will continue on to the next row.

Source: The Gift of Script: Bulk send emails from Google Sheet

Snippet, Solutions, Drive, Sheets

Bulk create Google Drive folders from a Google Sheet

Tags:
| |

The following Google Apps Script code was developed to bulk create Google Drive folders with data from a given spreadsheet (eg to name the folders and add permissions). It uses the Module Name and Marker to form the folder name as it iterates through each row in turn. At the same time it takes the Marker Email Address and uses the ‘DriveApp’ to provide edit access to the folder. Finally it creates another sub-folder in each folder as an extra.

Source: The Gift of Script: Bulk create Google Drive folders