Tag: JS set

Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Updated Feb 2022)

Google Apps Script, Google Sheets, Javascript

Have you ever wanted to have a Google Sheet available for only those users who need to edit and then once they are done, take their edit permissions away to maintain the integrity of the sheet?

I know that I have come across these conditions a number of times in the past. Maybe you have a task list that you want to send your team each time a task is allocated to them. Once they let you know that they are done, by say, entering a set of values or checking a task complete box on the row they need to work on, you want to be able to remove their edit permissions from your sheet.

In this post, we have created a Google Apps Script that will add and remove editors to a Google Sheet based on the spreadsheet’s data. More specifically, this script will:

  • Grab the users name and email in each row along with whether or not they have complete the task or if the Google Sheet has been shared and sent to the user.
  • Share the assigned user to the Google Sheet.
  • Send an email to the user. A separate Google Sheet tab is added to the sheet so an administrator can add their custom email message.
  • Automatically check a reference column of checkboxes indicating that the assigned user has been shared as an editor on the Google Sheet and an email has been sent to them.
  • Once the task has been complete the user check the “Edit Complete” checkbox in their assigned row.
  • Either automatically each day or when the Google Sheets administrator clicks the button,  each user who has completed all assigned tasks is removed from having edit permissions to the Google Sheet.

The best way to probably understand this script is through an example…

Continue reading “Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Updated Feb 2022)”

Google Apps Script: Get Google Doc Text Based on Reference Characters

Google Apps Script: Google Docs, Document App. Javascript: Spread syntax, Set, IndexOf, Substring

So here is the scenario, imagine you have a big Google Doc. You want to get a list of information from the document that you have noticed are between two sets of characters. Maybe something like this:

  1. You want to grab all the quoted text in a story and you know that the quoted test is between two sets of quotation marks: “ ”.
  2. You want to grab citations or asides inside different braces, for example, [],{} or ().
  3. You are making a mail merger and you want to grab a specific list of words that the user put in that is to be substituted based on special character identifiers, for example, {{name}}, {{phone}}.
  4. You want to grab all the websites in a Google doc and you know they start will start with “https://“ and with “.gov“ .

This tutorial provides a simple how-to do this.  Perhaps the code is exactly what you need for your project. We’ve set it up in a way that is easy to implement in your own project.

Continue reading “Google Apps Script: Get Google Doc Text Based on Reference Characters”