GAS – Add a custom menu

Adding a simple menu of scripts on your spreadsheet goes something like this:

The first part in ” is what appears on the menu, the second part is the function you are calling. Placing this code in the onOpen function automagically creates the menu each time you start or refresh your google sheet

GAS – Create PDF from Google Form Data, Save to Google Drive and Email It!

I’ll make some assumptions on this one, that you know how to create a Google Form and Responses sheet, and that you can create and edit a Google Doc

Create your Google Form with the questions you want. If you are inside a domain you can automatically collect the user’s email address too

Create a Google Document to use as a template. Leave space for user submissions, and in these spaces include a unique keyword for each answer Continue reading

GAS – format cell background

Easier than it looks!

GAS – format cell number format

In this case a date format:

GAS – format cell wrapping

This script sets the cell wrapping to on for the whole column. Useful for Google Form responses which over write existing cell formats

GAS – Get Array from Data Validated Range

Been happily grabbing data ranges from cell ranges in google sheets for ages, but never spotted that if you have a range where all the cells have data validation in them (e.g. a list of values or list from range), then even if they are blank, e.g. no selection has been made, GAS still counts them if you get the “length” of the array. This little snippet deals with that! Continue reading

GAS – Get the Last Row

Use this function to get the last row in Column A of a spreadsheet

If you want the first empty row, simply add 1 to ct:

GAS – Go to the last row in a sheet

Very often my data can run to hundreds of rows. To quickly go to the last entry use this script:

GAS – Insert Checkmark to Cell and Conditional Formatting

A little timesaver. Google Sheets doesn’t have a “Special Characters” selector, so if you want to use one you have to open up a Google Doc, copy the character you want, then back to Sheets to use it. This example shows a simple apps script for inserting a checkmark to a cell. We will then look at conditional formatting of the cell/cells. Continue reading

GAS – Insert new row in Google Sheet using script

Probably not as quick as going Edit > Add Row Above/Below, but provides the starting pointing for extending this little snippet to add content, copy down formulas or “setFormulas” to some of the columns. Useful when working with long lists. This script will add a new row above the existing active cell.

GAS – list files modified in the last 24 hours

If you need to keep track of what has been changed in your Google Drive, this script, set up with a time driven trigger in a google sheet is a lifesaver. Use it nearly every day. (Can’t claim the credit for this one!)

GAS – make a variable from a variable

Clever little ditty this one. Somtimes you need to create a dynamic variable on the fly, perhaps using data pulled in from a sheet or elsewhere that goes into a variable, but you then need to use that value as a variable name and assign it a value. This is how it is done:

I want the value of the variable value01 to become a variable name, result. I want to give result the value green, which is currently in the variable output01. If using a string in output01 you have to have the extra “‘” in the eval(), otherwise a number will not need them.

GAS – Move “Completed” Rows to separate sheet

Very often I create a google form and then need to complete further columns to give feedback or show progress. These can become unwieldy after a short time, so it helps to clear out and archive the finished rows to another sheet. In the example below there is a column that needs to contain “Yes” in order to be moved to the Completed sheet

GAS – random ID

In order to avoid repetition of filenames or clashes in dynamic variables, it is useful to generate a random string to add to the name:

You can then add it to your variable or string like so:

GAS – Restore Data Validation

A downside with GAS spreadsheets is the inability to share them and properly protect them at the same time, whilst remaining usable by an end user. So if I want an “unknown” and “untrusted” user to access the sheet and select from dropdowns etc., i must give them editing rights.

This example shows how to check and restore a cell validation each time the sheet is opened. Continue reading

GAS – Send a Simple Email

Very often you want to email either yourself or another user with output from a script. In very simple terms you need to provide the email address, subject and body

You can, of course, replace the text areas with variables:

GAS – Send an html Email

Much the same as sending a normal email, but this time you include a body with html tags:

GAS – Send Event to Calendar from Spreadsheet

Managing a Google Calendar from a spreadsheet is fairly straight-forward.
You do need the calendar ID and all your ducks lined up on the sheet.

in it’s simplest form:

Continue reading

GAS – Sheets – Clean and Clear Layout

Sometimes, we like our spreadsheet to give focus to what is on it. General formatting: colours, borders etc. do help, but even better is to remove all unneeded columns and rows, along with other distractions. For this to work, the top of the last row needs to be visible. Here is a finished example: Continue reading

GAS – simple data entry

You want to make life easy for your users, and reduce the opportunity for errors when entering data! This simple example allows a cleaning supervisor to enter their inspection scores (items clean / items dirty) for an area, calculates the percentage score, then moves the information to a second data sheet in a list. There is a drop down validation for the locations, and the date field validation allows a double click to bring up a mini calendar. The script is attached to the Submit drawing.

gas-data-entry

At the end of the script, all the fields are cleared ready for the next entry.

GAS – Sort a Single Column

You can do this with a flick of the wrist when working directly on a sheet, but to use a script to do the same thing is slightly more complicated! Google have decided in their infinite wisdom that if you want to sort a range, a column, you will want to sort the data around it as well. This is not always the case. Very often I have a sheet with columns full of unrelated lists: names, locations, dates, etc., and these often need to be sorted alphabetically, by themselves, in order for functions to work further down the line: match / vlookup / filter / index and so on. A few years ago, the clever people on the Google Docs forum came up with a solution, they said it only worked in G Suite for Work or Education, so you mileage may vary when running this in a standalone google sheet? Continue reading

GAS – Vertically Align Rows in Sheet

Another simple but useful one, particularly for better presentation of Form Responses, so pop this into your onFormSubmit script:

Google Sites (Classic) – Embed Live (working) Google Sheet “Range”

This one has been annoying me for a while but I finally arrived at a solution. It is especially usefeul when working inside a G Suite domain, and you wish to share information on a spreadsheet with some selectable cells (e.g. with drop downs) via a classic googlesites page, so for people with the right permissions they can use the functionality of the sheet without causing damage to the underlying data.

The default options and gadgets for presenting a spreadsheet offer too much of the sheet for my liking.

The way to sort this out is to create a web App that can be displayed on the page. The apps script is created on the website (Manage Site > Apps Script), and once the two parts are created inside a project, the app can be deployed and will then show up when added to you page.

So here is what you need to put in your code.gs Continue reading