AppsScriptPulse

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

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

[Google Sheets] Word Highlighter (made using Google Apps Script)

I have created a handy highlighting tool using Google Sheets and Google Apps Script. The specification of this tool is very simple.

  1. The user pastes a message into the certain cell (A2).
  2. The tool checks if the message contains some words which listed in another dedicated sheet(Spam Word List).
  3. 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.

Source: [Google Sheets] Word Highlighter

How to retrieve company info in Google Sheets with Clearbit and Apollo API

Image credit: Alexis Laporte

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:

  1. Create custom functions in Google Sheets
  2. Use Clearbit API to translate a company name into a company domain
  3. Use Apollo API to get details about a company using a company domain
  4. 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 👇.

Source: How to retrieve Company info in Google Sheets with Clearbit and Apollo API

Code preparation for Hash Code 2022 with Apps Script [snippets for working with .CSV data files]

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.

Source: Code preparation for Hash Code 2022 with Apps Script

How to get Linkedin profiles [and other Google Custom Search results] in Google Sheets with a formula

Image credit: Giacomo Melzi

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.

Source: How to get Linkedin profiles in Google Sheets with a formula

A Google Workspace Parents Evening Appointment System — by Baz Roberts

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)]

Repeatable Task [in Google Sheets]: To Script Or Not To Script, That Is The Question

Image credit: benlcollins.com

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

Based on CC-BY-NC https://xkcd.com/1205/

Source: Repeatable Task: To Script Or Not To Script, That Is The Question

Bulk rename files in Google Workspace/Drive with this new Add-on

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.

Bulk Rename Files Add-on Interface

Bulk Rename Files Add-on Interface

Source: The Gift of Script: Bulk Rename Files Add-on

Learn how to improve Google Sheets performance

Image credit: Google

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:

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.

Source: Learn how to improve Sheets performance

Archive Sheet data using tickboxes and Google Apps Script

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

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.

Source: The Gift of Script: Archive Sheet data using tickboxes