AppsScriptPulse

Official News, Solutions, AppSheet, Beginner

NEW! Calling Google Apps Script from an automation in #AppSheet

Tags:
| | 0 Comments |

Add custom integrations to your app using Apps Script. … You can use custom logic from within your AppSheet app by calling Apps Script from an AppSheet automation bot.

Examples of custom logic that you can implement include:

  • Create a calendar appointment when a button is clicked
  • Add a slide to a presentation when a new row is added
  • Save photos to Drive and share with specific individuals when uploaded using a form
  • Create an audit log by generating a Google Docs file with data from a table
  • Call an external service for machine learning prediction using data from a newly added row and write back model prediction

If you are not familiar with AppSheet it’s Google Cloud’s no-code platform that allows users to create custom applications that can be deployed across mobile and web, often setup using Google Sheets as a data source. AppSheet can be customised with workflows and automations, and those automations have recently been extended with a new preview integration to Google Apps Script.  The video in the Call Apps Script from an automation page provides a great summary of how the Apps Script integration works.

Source: Call Apps Script from an automation

Solutions, Gmail, Sheets, Video Tutorial, Beginner

Create multiple labels in Gmail with Google Apps Script

Tags:
| | 0 Comments |

With this reusable script you can create multiple labels in Gmail whenever you need to do so. All you have to do is create the script as shown in the video and then use it to create multiple Gmail labels in one go.

Continuing the Gmail label theme in this latest video tutorial from Chanel Greco you can learn how to batch create Gmail labels from data in a Google Sheet.

Solutions, Sheets, V8, Beginner

How to build an automated PDF report in Google Sheets using Apps Script 

Tags:
| | 0 Comments |

Grab this automated ConvertKit Report in Google Sheets to monitor your email list growth and use it to project your future list growth.

Ben Collins shares the solution he uses to produce a daily ‘ConvertKit’ PDF report. Even if you don’t use ConvertKit this post is still worth a read as the basic model of getting data from a third party API into Google Sheets and sending a PDF summary could come in handy. Perhaps not surprising given this post comes from Ben it includes some clever Google Sheet functions to prepare and shape data.

Source: Automated ConvertKit Report In Google Sheets

Solutions, Sheets, Video Tutorial, Beginner

Google Apps Script: How to export Google Sheets as PDF – YouTube 

Tags:
| | 0 Comments |

In this video you will see how you can use Google Apps Script to export a Google Sheets document as a PDF

It’s been a while since we featured a video tutorial from Chanel Greco, but that doesn’t mean she hasn’t been busy. In this recent tutorial from Chanel you can learn how to use Google Apps Script to export a Google Sheet as a PDF Document. You can watch this and other videos from Chanel on the saperis YouTube channel.

Source: Google Apps Script: How to export Google Sheets as PDF

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, Sheets, V8, Beginner

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

Tags:
| | 0 Comments |

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

Solutions, Sheets, Beginner

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

Tags:
| | 0 Comments |

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

Solutions, Drive, V8, Beginner

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

Tags:
| | 0 Comments |

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

Solutions, Sheets, V8, Beginner

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

Tags:
| | 1 Comment |

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

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