AppsScriptPulse

Beginner, Sheets

How to combine arrays of data into one Google Sheet with Google Apps Script

Tags:
| | 0 Comments |

In this post, we’re going to look at how we can easily combine arrays of data on separate sheets in a Google Sheet and add them on one single sheet. We’re going to use the relatively new spread operator (…) to do this.

A little knowledge about data manipulation can get you a long way, particularly if you are doing lots of work in Google Sheets. This post from Baz Roberts is an opportunity to learn about spread syntax (also known as the spread operator) in arrays. Have a read and hopefully you’ll see the benefits.

Source: How to combine arrays of data into one sheet – this post is also available to read on bazroberts.com

Solutions, Sheets, V8, Beginner

An automatic random team maker using Google Apps Script

Tags:
| | 0 Comments |

In this post, we’re going to look at a script to be able to make even teams from a selection of players. I play football every Sunday and every week my friends sign up to play and most weeks there are different players playing, so we end up having to make the teams, and trying to do it as fairly as we can, which inevitably takes some time.

Nice ‘weekend project’ from Baz Roberts highlighting how Google Apps Script can be used to do a weighted random shuffle of data. The post includes a line-by-line explanation of what the script is doing and a great resource if you are at the beginning of your Apps Script journey.

Source: Automatic Team Maker — Apps Script (also available on bazroberts.com)

Solutions, Sheets, V8, Beginner

Quickly format multiple Google Sheets with Apps Script

Tags:
| | 0 Comments |

In this post, we’re going to see how we can very quickly format multiple Google Sheets with a little help from Apps Script.

The idea of this post, came from the Department of Education in Hawaii, where they have over 300 schools and a guy who works there told me he had to format over 300 Google Sheets the same way, and wondered how this could be done with Apps Script.

Easy I said! And I thought it was a typical example of how a little knowledge of Apps Script can help you speed up your work and allow you to spend less time doing boring, repetitive work, and focus more on the data itself.

Let’s see how it’s done!

This is a nice real world example of how Google Apps Script can be used to make a manual routine task more efficient. The post by Baz Roberts includes a detailed explanation of what is going on in the code making it also a great learning resource. If you have some more complicated Google Sheets formatting in mind you might want to also look at Google Sheets Macros, which lets you record every action you take within the spreadsheet creating a script you could call similar to the format functions included in the post.

Source: Quickly format multiple Google Sheets with Apps Script (also available on bazroberts.com)

Solutions, Drive, Sheets, V8, Intermediate

Importing data from MS Excel to Google Sheet with Google Apps Script

Tags:
| | 0 Comments |

In this post we’re going to look at how we can get data from an Excel spreadsheet and import some of it into a Google Sheet. As an example, we’re going to upload an Excel which contains the current month’s royalty data from Amazon and add it to a central sheet which contains all the previous month’s royalties.

Similar to the post on converting a PDF document to text, this tutorial from Baz Roberts uses a similar approach of using the Google Drive API for file conversion, this time converting a MS Excel file into Google Sheets to make it possible to easily extract and update a master spreadsheet. The post includes a detailed explanation of the shared code.

Source: Importing Amazon royalty data from Excel to Google Sheet – Learning Google Workspace & Apps Script

Solutions, Forms, Sheets, V8, Beginner

Apps Script Basics – Using Form Responses – Learning Google Workspace & Apps Script

Tags:
| | 0 Comments |

In this post, we’re going to look at how we can work with the responses a form user submits.

We’ll look at two main ways, 1) Getting the form responses from a Google Sheet, 2) Getting the form responses directly from a Google Form.

To show some practical uses of this, we’ll do the following:

  1. Set up a simple problem-reporting log, which will email the relevant parties the problem in a classroom. We’ll do this via the Sheet and via the Form.
  2. Get students’ pieces of writing submitted via a Google Form and copy them to their individual sheets, ready to have feedback added.
  3. Set up a simple appointment system, which will update itself as people take the appointments, leaving only the available ones on the Form.

This post taken from Barrie Roberts’ latest book “Beginner’s Guide to Google Apps Script 2 – Forms“, available on Amazon here. The post covers some different ways that you can interact with Google Form responses and contains lots of useful code and tips that might be useful to other Google Apps Script developers.

Source: Apps Script Basics – Using Form Responses – Learning Google Workspace & Apps Script

Solutions, Forms, Sheets, Beginner

A Google Workspace Parents Evening Appointment System — by Baz Roberts

Tags:
| | 0 Comments |

This is the first of a three-part post, where we’re going to look at how you can create an appointment system using Google Forms and Sheets and with the use of Apps Script, how you it will update the available times on the forms and how it will send automate confirmation emails to those making the appointments. I’ve used this system for parents evening meetings, but it could be adapted for any area that need appointments.

This series of posts from Baz Roberts details an appointment booking system created in Google Sheets and Forms. There is a lot of details, tricks and tips across the post series so well worth spending the time to unpick what has been shared.

Source: Parents Evening Appointment System [Alternative link: Parents Evening Appointment System (bazroberts.com)]

Solutions, Forms, Sheets, Beginner

Issues reporting with translation [using Google Apps Script and Google Forms/Sheets

Tags:
| | 0 Comments |

In this post Baz Roberts highlights the benefits of Google Apps Script Language Service to translate Google Form responses into another language.

Source: Issues reporting with translation

Snippet, V8, Beginner

Google Apps Script Basics – Variables and getting & setting values

Tags:
| | 0 Comments |

Image: Baz Roberts

In this post, we’re going look at how variables are set up and how they can store various pieces of information. We’re also going to look at reading and writing data from a spreadsheet, which is one of the most common tasks when working with one.

We offer a wide range of community contributions here on AppsScriptPulse. This one might be useful if you are just learning to code and want to understand some of the fundamentals or if you’ve been using Apps Script for a while learn about variable types available in the V8 runtime.

Source: Apps Script Basics – Variables and getting & setting values

Solutions, Sheets, Web App

Moving Card Game using Google Sheets & Apps Script

Tags:
| | 0 Comments |

Moving Card Game using Google Sheets & Apps Script

One popular method to get your students to learn is to categorise things or to put them in order. Here, we’re going to look at making a card sorting game, which could be used for both.

Source: Moving Card Game using Google Sheets & Apps Script

Solutions, Web App

How to make a Ladder Card game with Apps Script

Tags:
| | 0 Comments |

In our foreign language classes, one of the classic activities we do to help students learn new language, is to print phrases onto card, then cut the cards up. We then write the answers on the back of the cards. They then place the cards on a desk and then go through the cards one by one, trying to remember the correct answer.

Here we’re going to make a simple ladder card game using Apps Script and Google Sheets.

Source: How to make a Ladder Card game with Apps Script