AppsScriptPulse

Apps Script V8 Runtime Explained For Non-Professional Developers

Learn how to use modern JavaScript features in your Apps Script code with the release of the Apps Script V8 runtime engine.

Somehow we missed this post from Ben Collins when it was originally published in February, 2020, but it is well worth visiting and adding to our V8 collection of posts. For those still catching up V8 is the new runtime for Google Apps Script which enables modern JavaScript syntax to be used in your code. Ben covers some of the basic differences now possible with V8, with lots of examples.

Source: Apps Script V8 Runtime Explained For Non-Professional Developers

Understanding Macros and Apps Script

Macros are small programs that a Google Sheets user can create to complete repetitive tasks. In fact, macros are an excellent way for a user to become familiarized with Apps Script, as a snippet of code is saved in the script editor each time a macro is recorded. Recording a macro is as simple as going to Tools → Macros → Record macro.

Recording macros and assigning shortcuts to them is a great way to execute repetitive tasks and become a more efficient Sheets user. You can also import macros from other workbooks so you can perform the same tasks and assign the same shortcuts across all of your workbooks.

Source: Understanding Macros and Apps Script

How to Send SMS Messages with Google Sheets and your Android Phone – Digital Inspiration

Send personalized text messages to your contacts with the help of Google Sheets and your Android phone. The SMS messages go directly from your phone SIM, no third-party SMS service is required.

Amit Agarwal (@labnol) has highlighted a really interesting model to integrate G Suite with Android mobile apps using Google Apps Script and MIT’s App Inventor. In Amit’s example he illustrates how you can quickly create your own Android app as a proxy for sending SMS messages from a Google Sheet. Given the flexibility of App Inventor this opens up numerous other opportunities to integrate G Suite and your Android phone.

Source: How to Send SMS Messages with Google Sheets and your Android Phone – Digital Inspiration

Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph) – Yagisanatode

Have you ever woken up in the middle of the night thinking – phawh! I’d really like to know how many words there are in a paragraph – Or stopped mid-chew on some arbitrary item carelessly left in the paddock by the boss and thought – geez! If there was only a way to count the number of sentences in a paragraph in a cell in Google Sheets!?

In this short tutorial, Yagisanatode will show you how to get the total count of words in a cell and the total count of sentences in a cell in Google Sheets. They even have a how-to on getting the average sentence length in a paragraph.

Source: Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph) – Yagisanatode

TSFormBot — A Google Form Hangouts Chat Bot powered by Google Apps Script

TSFormBot is an Apps Script powered Google Form Hangouts Chat Bot which creates form submission webhook notifications for Hangouts Chat Rooms.

Need a structured ping on your Hangouts chat whenever someone submits a form? Laura Taylor has built a perfect solution with an ever more easier workflow documentation on how to implement it.

Source: techstreams/TSFormBot

Merging two 2D arrays/datasets/tables with Google Apps Script – Andrew Roberts

Ever needed to merge two similar datasets and automatically get rid of any duplicates?

This article describes how to do that using the SsObjects Google Apps Script library.

Source: Merging two 2D arrays/datasets/tables with Google Apps Script – Andrew Roberts

Scrape and save data to Google Sheets with Apps Script

If you are looking for a way to get data that is displayed on a website and save it to a Google Sheet every <some time interval>—because a record number of cmd+x and cmd+v keystrokes aren’t really the stats that resonate with you and your friends—please, read on.

A detailed guide by Kamie Robinson on how to scrape data from a website, tabulate that on a Spreadsheet and a sweet tip to backfill your data!

Source: Scrape and save data to Google Sheets with Apps Script

Track Coronavirus (COVID-19) Cases in India with Google Sheets – Digital Inspiration

The Coronavirus (COVID-19) tracker uses sparklines in Google Sheets to help you visualize the spread of the coronavirus outbreak across different states of India over time.

You can always depend on Amit Agarwal (@labnol) to come up with clever solutions. In this post Amit outlines how he uses Google Apps Script to collect/parse data from the Indian Government website so you can track COVID-19 cases. Already Andrew Roberts has used Amit’s solution to create another version of the tracker for Wales (UK). Ping us at @AppsScriptInfo if you make a variation for your own region and we’ll list it in this post.

Source: Track Coronavirus (COVID-19) Cases in India with Google Sheets – Digital Inspiration

Tuning your Google Apps Script experience (for Google Sheets on steroids)

Are you automating Google Sheets using Google Apps Script? Automation is awesome but doing it with the Google tools can be cumbersome.

Interesting article by José Miguel Gutiérrez that provides some amazing tips on how to improve your coding experience while working with Spreadsheets and Google Apps Script.

Source: Tuning your Google Apps Script experience (for Google Sheets on steroids)

Google Apps Script: Add and removed Google Sheets columns based on a search array

Have you ever wanted to delete or add columns in a Google Sheet, based on another set of Sheet data?

In this tutorial, we’ll show you how to use Google Apps Script to update your headers based on another sheets values. These sheets values can come from the current Google Sheet workbook or another one. We will also ensure that the data below the headers is migrated along with the new header location.

Source: Google Apps Script: Add and removed Google Sheets columns based on a search array

How to automatically pull Facebook Ads data into Spreadsheets

After seeing how to send your Google Ads campaign data to Spreadsheets we will do the same with Facebook Ads. Once again, the idea is to implement a data-driven strategy in campaign performance management. But in my opinion, monthly or even weekly reporting are not enough: it has to be daily. And so that it doesn’t take you 20 minutes a day to export your CSV files and format them, here’s how to automate the boring stuff.

Pretty thrifty approach by Henry-Paté Nicolas on tracking Facebook Ads on Spreadsheets using Google Apps Script.

Source: How to automatically pull Facebook Ads data into Spreadsheets

Replays from SheetsCon 2020 – the world’s first, dedicated online conference for Google Sheets users

SheetsCon 2020 was the world’s first, dedicated online conference for Google Sheets users on 11th – 12th March 2020.

We had 11 world-class experts talk about how they craft solutions using Google Sheets and G Suite.

Over the course of two jam-packed days, we had everything from deep-dive tutorials on specific topics to cutting-edge solutions across different industries. We had 1-on-1 networking opportunities, roundtable discussion rooms with other professionals and an Expo Hall with vendors at the forefront of the Google Sheets movement.

Listen to all the replays and access all the templates when you enroll for free.

Whilst this was Google Sheets focused there are a number of sessions that cover Google Apps Script and G Suite Add-on development. As well as the replays there are a number of linked resources you can also use.

Source: SheetsCon 2020 Replays

How to Generate a Report of Bounced Email Addresses in Gmail with Google Apps Script – Digital Inspiration

The bounced email report for Gmail lists all the email address that bounced. It also provides the response provided by the recipient’s server for rejecting your email.

Nice script example shared by Amit Agarwal for getting a bounced email report from your Gmail account.

Source: How to Generate a Report of Bounced Email Addresses in Gmail – Digital Inspiration

A Walk A Day Keeps The Doctor Away #GoogleAppsScript – plotting lat/long data on Google Maps

Nice little script snippet from Mariette Timmer which takes a .csv file with latitude/longitude points and plots them on a Google Map and sends the resulting image embedded in an email.

Source: A Walk A Day Keeps The Doctor Away #GoogleAppsScript

G Suite Solutions —  Apps Script Powered Workflow Automation

Laura Taylor recently spoke at SheetsCon on the topic of Automation with Apps Script. During the presentation, she walked through a simple approval workflow for a hypothetical small business to demonstrate the power of Apps Script to automate workflow inside G Suite. She also showcased modern ECMAScript features supported by the new Apps Script V8 Javascript runtime.

The article is an overview of the workflow covered in the author’s presentation along with install instructions for trying it out in your G Suite domain.

Source: G Suite Solutions —  Apps Script Powered Workflow Automation

Building your first Hangouts Chat Bot in Apps Script

The task of building a Google Hangouts chatbot can often seem daunting due to the diverse technology options and approaches. What is often not always known is how relatively easy it is to build chatbots by simply using Google Apps Script.

Here’s a step by step guide on how to build your first Google Hangouts chatbot using Apps Script.

Source: Building your first Hangouts Chat Bot in Apps Script

API Calls to ServiceNow from Google Apps Script with OAuth

Have you ever thought that it might be useful to connect a Google Apps Script to your ServiceNow instance? Perhaps you’d like a new way to quickly download a list of configuration items from your change management database to a sheet, or you’d like to upload a selected range of cells from a sheet as new records in SNOW, one record per row?

This guide is focused more on the coding than how to configure your identity provider and ServiceNow.

Source: API Calls to ServiceNow from Google Apps Script with OAuth

Google Developers Blog: Evolving automations into applications using Apps Script

Two years ago, our team began building custom software to automate the multi-stage and highly manual team staffing process. Building internal software has allowed the same-size Staffing Operations Team (3 members!) to enjoy a 60x reduction in time spent staffing each role.

The Apps Script ecosystem has emerged as the most critical component in our toolkit for building this internal software, due to its versatility and ease of deployment. We want to share how one piece of the staffing process has evolved to become more powerful over time thanks to Apps Script. Ultimately, we hope that sharing this journey enables all types of teams to build their own tools and unlock new possibilities.

Source: Google Developers Blog: Evolving automations into applications using Apps Script

Students use Google Apps Script to quickly identify overused words in a body of text

Great to see some new young Google Apps Scripters at work

Manage new employee equipment requests within Google Sheets – Sheets to Apps

In today’s episode of Sheets to Apps, @TechandEco will show you how to manage new employee equipment requests with Google Sheets.

See the video description for all the links you need including the reference documentation.

Subscribe to Apps Script Pulse...