Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 2                                         <<Intro>>

In our last tutorial, we grabbed the Google Sheets count data from a single rating survey question. We then displayed it two ways; horizontally with the question item at the top, or vertically with the question item to the side.

Google Sheete count data from rating survey

In part 3 of our Google Sheets data transformation with Google Apps Script course we are going to expand on our current work and add two more rating question items from our survey. Each survey will have the same 1. Weak to 5. Strong survey items.

Our new list of question items will include:

  • Rate your goat’s athleticism.
  • Rate your goat’s agility.
  • Rate you goat’s headbutt

All the information any self-respecting human companion for goats would know.

We’ll be updating our code to be able to take any number of item questions so long as they have the same choices.

Continue reading “Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets”

Google Apps Script Course – Part 2: 2D Array Data Transformation of Single Question Item Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 1                                         <<Intro>>

In our previous tutorial, we created a 2d array of count values for each item chosen in a survey form in Google Apps Script. In our survey, we asked users to submit what type of goat they are. We didn’t know what species of goat they identified as so we just needed to count for any goat species that was submitted.

In part 2 of our course, our Google Sheets survey data is a little different. This time around we are asking the human companion of their coding goat to:

Rate your goat’s athleticism.

Respondents then rate their goat’s athleticism on a 5-point scale:

  1. Weak
  2. Below Average
  3. Average
  4. Better Than Average
  5. Strong

But, Yagi! Can’t we simply use the script in part one?

Sure, you could. However, you might come across a bit of a problem. In Part 1 we generated our choices for our count based on their appearance in the survey. What happens if none of the respondents rated their goat as Weak (This is right and just)? Weak would not be recorded in our 2d count array when we ran our Google Apps Script code.

Further, if the first user in our Google Sheet response data rates their goat as Strong, then the first choice in our 2d count array will be Strong.

That would just look weird for a summary count of a rating survey. We really need to display our count in order from 1.Weak through to 5.Strong.

Continue reading “Google Apps Script Course – Part 2: 2D Array Data Transformation of Single Question Item Data to Total Count of Rating Choices in Google Sheets”

Google Apps Script Course – Part 1: 2D Array Data Transformation of Single Question Item Data to Total Count of Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<< back to Introduction                                                                            Part 2  >>

Hey, you made it! It’s time to do some coding. You know, the fun stuff.

In this part of the 2-dimensional data transformation course, we are going to get the total count of results of some form data on a Google sheet. The form data contains a single question item with a selection of different unordered choices.

To get the most accurate data we could we surveyed goats online and asked them what type of goat they are. Here is a sample of the results:

what type of goat are you - Google Sheets Survey

Continue reading “Google Apps Script Course – Part 1: 2D Array Data Transformation of Single Question Item Data to Total Count of Choices in Google Sheets”

Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

Note! This course requires a rudimentary understanding of very basic Javascript concepts and of what an array is. Nevertheless, if you want to come in blind you should be able to pick things up with a bit of Googling. Alternatively, if you just want the neat code snippets for your own project, simply skip the course stuff and copy and paste away. 

~Yagi

Introduction: 2D Array Data Transformation for Google Sheets

One of the major regular tasks that Google Apps Scripts can do for Google Sheets is to automate the transformation of data from one state to another.

Sometimes this task can simply be accomplished by building dynamic templates using the vast functionality of Google Sheets. Other times automation via Google Apps Script is a better tool for the job.

When working with Google Sheets data in Google Apps Script you are immediately going to be met with the 2D array. Take a look at this simple range of Google Sheet data from A1:B2:

A1 to B2 Google Sheet Data

If we grab this data in Google Apps Script, it will appear like this:

As you can see, we are looking at a nested array that is two levels deep commonly known as a 2-dimensional array.

Remember that while our spreadsheets rows and columns start at 1 our arrays start at 0.

Spreadsheet data is transformed into a 2d array first by row and then by column data. In the above example, we can see that row 1 is the zeroeth element of our array and that zeroeth item contains column A (‘Goats’) and column B (‘are’) data items. Then in the 2nd row or array element 1, we get those two column values of ‘awesome’ for column A and ‘creatures’ for column B.

Now we have the basics out of the way, let’s dive into transforming this data.

In this course, we are going to take a bunch of survey data in our Google Sheet and get the total count of each selection from the survey data responses using Google Apps Script and then redisplay it back into our Google Sheet.

Continue reading “Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets”

Google Apps Script: Filling out your column data to match the number of columns in a range

Google Apps Script, Google Sheets

Have you ever experienced this warning error when trying to set values or paste a range of values back into your Google Sheet with Google Apps Script?

The number of columns in the data does not match the number of columns in the range. The data has 6 but the range has 9. (line 52, file “test”)

Yeap. One of your row lengths is not as long as the width of the column range you set.

This generally occurs when you are manipulating data into a new format before setting it back into your Google Sheet.

Let’s look like some dodgy data I’ve prepared that I want to paste or set backing my spreadsheet.

As you can see, I’ve got a 2d array for data with 7 rows and with a max row width of 9. This means our data will extend to nine columns at its max.

Now, your first instinct might be that if I take a few chips out of square peg, it should still fit into a square hole. I mean, I’m only subtracting from the square, right?

Then you go off blazing a path through the code, hoody hanging low over your head, punching away at your bespoke mechanical keyboard.

With victory within your grasp, you hit run and your world comes crashing down.

column in data does not match number of columns in range Google Apps Script

The Solution

Continue reading “Google Apps Script: Filling out your column data to match the number of columns in a range”

Create a seat booking form with Google Forms, Google Sheets and Google Apps Script

Google Forms, Google Sheets, Google Apps Script: SpreadsheetApp, FormApp

In this tutorial, we are going to build a simple seat booking Google Form. It will contain a registeree’s name and the session that they wish to attend.

Each time a registeree submits a request to book a seat for a session, that seat is taken from the list displaying only the remaining seats for that session for when the next user submits the form.

Take a look at the example below:

Basic Seat Booking Form Google Forms

If all the seats have been booked for a seminar, then that seminar will not appear on the form. If all seats have been booked for all seminars, then the Google Form will close.

We will even create a  live list of attendees that we can embed on our website using Google Sheets.

Google Forms can’t do this for you out of the box. We will need to use Google Sheets to store our bookings and do some manipulation of the data. Then, we will use Google Apps Script to update the Google Form with the number of seats each time the form is submitted.

The Google Apps Script script has been prepared in a way that someone with limited coding skills can use it as a template by plugging in a few global variables. Alternatively, a more advanced user can incorporate the code into a larger project.

I’ll assume you have the basic abilities to create a simple Google Form and have used Google Sheets.

This tutorial can be followed as a useful step-by-step guide or if you are more confident, you can simply jump to the code using the contents below.

Continue reading “Create a seat booking form with Google Forms, Google Sheets and Google Apps Script”

Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite

Google Apps Script, G Suite Admins, Google Calendar, Calendar API,  AdminDirectory, GroupsAp

Note! This article is for G Suite users and admins. You won’t be able to apply the same code to Consumer accounts. 

Google Groups in  G Suite can be a really convenient and clean way to share users to drive folders and file locations, email users and set up Google+ community locations. However, I have come across some issues with sharing Google Calendar to Google Groups.

When you assign a Google Group email to a Google Calendar it sends out an email inviting the users in the group to accept the calendar into their list of shared calendars. The user must then accept the invitation before the calendar is added to their Google Calendar list of shared calendars…calendar.

The users receive an email message like this:

Hello Goat_GroupAlpha,

We are writing to let you know that Billy.Goat@yagisanatode.com has given you access to view events on the Google Calendar called “Test”.

Click this link to add the calendar.

– The Google Calendar Team

The Problem

In a large organisation, not all users in a group will add the calendar using the link. This might be okay but if the calendar is important or the user, as is often the case, simply just forgot to add it, then there will be emails to you the admin in the future asking you why they don’t have the calendar that everyone else has and has now missed some important event…sigh.

Another issue is that when a new user is added to a group when the group has already been added to a calendar, then that user will not receive an email invitation to the calendar and won’t be able to see the calendar.

Almost a Solution

Continue reading “Google Apps Script: How to force subscribe Google Group Members to a Google Calendar for G Suite”

Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides

Google Apps Script: Dev Tools, Color Picker, Side Bar, Custom Prompt, HtmlService, onOpen, Sidebar, Dialog Box

I wanted to update one of my free Google Add-on apps that works with colour. What I had is just the standard HTML color input element where the user selects from the palette and that hexadecimal colour code is returned to Google Apps Script to be used in the App. The problem is that it is really hard to get a good colour match between the palette and Google’s own colour range that is accessible from the fill or text colour buttons.

Take a look at the comparison between the HTML color input element and the Google Sheet background colour palette in the image below.

Color input vs google sheet palette colours

That’s not a user-friendly tool to match colours with the standard Google palette.

So in the back of my mind, I had always wanted to create a tool for a sidebar or dialogue box that would allow the user to easily access the standard colours or use the custom palette provided by the HTML color input.

After finding a bit of time in my recent summer break I came up with this.

Google Color array picker

And this is how it looks in action:

Continue reading “Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides”

Google Apps Script: Hexadecimal Color Codes for Google Docs, Sheets and Slides Standard Palette

Google Apps Script – GSuite Standard Color Palette

I am planning on updating a few Google Apps Script projects and updates soon. To accomplish them, I needed to get the full array of colours and their hexadecimal codes from the Google Sheets, Docs and Slides dropdown menus for the text and fill colours.

Google Sheets Color Palette

I wanted to be able to easily access the hexadecimal codes for each of the custom colours that Google has.

In this short post, I want to share a number of array and object formats along with a quick Logger.log example for each one.

However, first of all here is the Google Sheet with the colours along with their hexadecimal name and the name that Google gives the colour.

You can check out the file here:

Continue reading “Google Apps Script: Hexadecimal Color Codes for Google Docs, Sheets and Slides Standard Palette”

Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets

Google Apps Script: isChecked(), switch, filter, map

One Checkbox to Rule them All

Now that’s a fantasy novel I could be my gums into.

Have you ever created a Google Sheet projects where you could really use a select all checkbox (they call them ‘Tick-boxes’ in Google Sheets)? Sure you can copy a range of tickboxes and paste the same range etc. But can you really trust your users not to mess that up?

People are used to select-all checkboxes in their computerised lives. It’s always better to work with familiarity to provide a better user experience rather than try and teach the user on the fly how to do something your way.

With this in mind, I went about creating a select-all checkbox for Google Sheets. Here is a little demo of how it all works. All the black background Tick boxes are select All boxes. These boxes have been assigned a range of other checkboxes that will be either checked or unchecked depending on the main select-all boxes state.

Google Select All Demo with Google Apps Script
Select All Demo

As you can see, the select all checkbox only changes the tick boxes in the assigned range. It does not affect any other non-tick box data in the same range.

To get this up and running on your own project, all you need to do is copy and paste in the two code files in your Google Apps Script editor. The first code file is the function that runs the check-all code. The other file stores all the select-all checkboxes and the ranges that they will affect.

Let’s take a look at the code and then run through a quick use guide before finishing off with an example.

Those of you who want to dive into the nuts and bolts of the code, there will be more explanation of each element of the code at the end.

The Code

Continue reading “Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets”