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:
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.
Get students’ pieces of writing submitted via a Google Form and copy them to their individual sheets, ready to have feedback added.
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.
I have created a handy highlighting tool using Google Sheets and Google Apps Script. The specification of this tool is very simple.
The user pastes a message into the certain cell (A2).
The tool checks if the message contains some words which listed in another dedicated sheet(Spam Word List).
The tool highlights (bold and color red) the found words in the message.
Here is a nice post demonstrating how you can use Google Apps Script to highlight keywords within a Google Sheets cell. The solution uses SpreadsheetApp.newTextStyle() to create a custom style which is then applied to part of the cell text.
Scripting in Google Sheets gives you super power, and it’s very easy to use APIs to enrich your document.
In this article I’ll show you how-to:
Create custom functions in Google Sheets
Use Clearbit API to translate a company name into a company domain
Use Apollo API to get details about a company using a company domain
Bonus: Use Google CacheService to avoid API rate limits
We recently highlighted a Medium post on How to get Linkedin profiles in Google Sheets. Here is a similar example which combines data from the Clearbit and Apollo to return company information including other social media urls and more.
Image credit: Alexis Laporte
Some great tips in this post including using the Cache Service, find out more via the source link 👇.
Apps script code to manage CSV files input and output for hash code 2022 of Google.
This post from Stéphane Giron provides some very useful Google Apps Script snippets for working with .CSV data files. Included in the post are ways to:
Retrieve CSV files with Apps Script and read data;
Treating CSV files; and
Create output CSV files
As a bonus the post shows you how you can create a backup text file of your apps script file.
Some time ago, I made a custom Google Sheets formula to automatically get the Linkedin profile of a person/company. It can be helpful to anyone using Google Sheets as a CRM or recruitment, product feedback, or marketing tool. It saved me a lot of boring copy/pasting, so hopefully, it can save some time for you as well
Interesting project from Giacomo Melzi that uses the Google Custom Search API to search LinkedIn for personal and company profile pages. As noted by Giacomo:
Since Linkedin profiles are behind a login (you have to be logged in to check someone’s profile), the formula relies on a workaround. The script performs a domain-constrained search via the Google Custom Search API.
That’s something anyone can do manually in a standard Google search. Just try adding site:{domain} before your search query, and your results will be filtered by domain.
This technique can be used on other sites that are behind a login but indexed by Google. A couple of important limitations to be aware of, in particular, free quota limits. Check out the source post for more details and the code to run this yourself.
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.
A framework for thinking about whether you should automate a repeatable task in Google Sheets with scripts or not
Ben Collins provides some great advice on when you should think about automating tasks in Google Sheets. Click through the source link at the bottom for the full post for some great tips.
A graphic that stood out for me was the XKCD comic strip below.
… the math didn’t quite add up in my head so I double checked
Google Add-on to bulk rename Google Drive files quickly and easily in a given folder. You define exactly what you want to search for in the file name and the text to replace it with. Includes instructions and demo video.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
You can improve the performance of Google Sheets and speed up calculations.
Via Sourabh Choraria (@schoraria911) we picked up this tweet from
Michael Avrukin who is working on the Google Sheets help center documentation:
Hi there dedicated #sheets users, we are starting to work on our help center content – here is our first article in the new format. I’ll appreciate thoughts / feedback anyone has. #googlesheetshttps://t.co/1wJ5abJZiI@benlcollins
The page includes a number of useful tips to speed up Google Sheet calculations and whilst not specifically Google Apps Script related provides useful information on some things you can look out for when your Google Sheet starts getting slow.
Automatically move a row of data from one Google Sheet tab to another when a tickbox is selected.
Archive a row of data by ticking a box
The following Google Apps Script is designed to move a row of data from one Google Sheet tab to another when a tickbox has been selected in a given column. The tool was developed to help colleagues with automatically archiving data that had been actioned.
The tool uses an ‘onEdit(e) simple trigger’ to constantly monitor the Google Sheet for any changes in values. Is it specifically targeted at a tickbox-column only so that it does not archive rows of data on every edit of the Google Sheet.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
