Google Sheets Beginners: Paint Format (10)

Google Sheets: Paint Format

Want to know a quick way to update a cell or range with an already existing format in your Google Sheets? Paint Format is here to the rescue.

Google Sheets Paint Format button

This versatile tool will help you to quickly replicate font, alignment, border, rotation and other styling formats in your Google Sheet.

In this tutorial, I’ll cover,
1. What the Paint Format button is and what it does.
2. Formatting one cell to another.
3. What things Paint Format will format across.
4. Using the formatting from one cell to update many cells.
5. Formatting from multiple cells to multiple cells
6. What Paint Format won’t copy across.

Check out the video below:

Watch this video on YouTube.

Want a solid step-by-step course to become a pro at Google Sheets? Udemy has some professional courses that will turn you into an admin ninja!

I’m a huge fan of Justin Mares, Mastering Google Sheets course. Sign up today*

~Yagi

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Creating Unique Ranges from 2D Arrays in Google Apps Script

Google Apps Script, Google Sheets, Javascript 

If you have ever done any work in Google Sheets you might have come across the incredibly useful UNIQUE function. This function can take a single row or multiple rows and generate a list of unique cell values.

UNIQUE demo Google Sheets

Sometime you might have a use case where you need to replicate the UNIQUE functionality in Google Apps Script. Maybe you are drawing in data from another source like an API, BigQuery or a database, or presenting your unique data in another format.

Whatever the case, knowing how to to create a unique array from a 1-dimensional array or a 2-dimensional array is an important part of your toolkit as a coding goat 🐐. This tutorial will look at how to create this with modern Javascript that is incorporated into the Google Apps Script language. And of course, we’ll focus our tutorial more on a Google Apps Script because that’s kinda our thing.

We’ll start off with a very basic example of creating a unique array from a 1-dimensional array in our code:

UNIQUE Array generated from a 1d array in GAS

…and then build ourselves up to a final example by grabbing multi-column range of Google Sheet data from which we create a unique list and then display the total sum corresponding to each unique cell value in each of our associated unique columns. Something like this:

UNIQUE 2-Array of multiple columns with result set in GAS
Click to Expand!

As we go through our examples I’ll display the runtime the entire code. Runtime is not a perfect indicator of the performance of the script there are other factors that will influence the runtime, but it will do for our purposes. I think you will be surprised by how fast these scripts run.

Before we get started, let’s take a quick look at the sample data…

Continue reading “Creating Unique Ranges from 2D Arrays in Google Apps Script”

Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data

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”

Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code

Google Apps Script: SpreadsheetApp,  addEditor/s, removeEditor/s, alert. Javascript: try…catch, forEach, join, push, template literal. Google Sheets

In this tutorial, we will go over the basics of adding users as Editors to Google Sheets with Google Apps Script. We’ll go through the process step-by-step, starting with two very basic codes and then progress on to error handling so your code doesn’t break for your user.

In Google Sheets just like Docs, Slides, Forms and Sites you can add co-editors to work on your projects. This is usually done straight from Google Drive or within the chosen Google file in the top right with the Share button.

Share button Google Sheets

Google Sheets Share Edit permisssions

The rules for sharing a specific user as an editor are pretty simple. The user must have either a Gmail (name@gmail.com) account, GSuite for Education domain account (name@yourdomain.com) or Google Workspace (formerly, Gsuite) account with an email in the workspace’s domain (name@yagisanatode.com).

Now that we have all the basics, let’s go ahead and write some Google Apps Script code. First of all, open a Google Sheet. It can be one that you want to use to add and remove editors with code on a project you are working on or just a practice Google Sheet. Then go to Tools > Script editor.

Continue reading “Google Apps Script: How to Add and Remove Editors to A Google Sheet with Code”