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

Apps Script V8: Multiple script files and namespaces – Desktop liberation

Apps Script doesn’t have a module loader system. If you’re used to developing in NodeJs, you’ll also be familiar with importing and exporting to include required modules in your project. With Apps Script, you have no control over the order in which multiple script files are executed. In Legacy Apps Script, there seemed to be some kind of workaround going on so that global statements were executed in a sensible order (I don’t know the details), but in V8 this is not the case. …

My golden rules are

  • Nothing executable should be in global space
  • Don’t rely on the order that things are processed
  • Minimize the number of executable functions (1 is good)
  • Always assume your code will be reused somewhere else.

Source: Apps Script V8: Multiple script files and namespaces – Desktop liberation

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

Apps Script V8: spreading and destructuring – Desktop liberation

V8 adds destructuring from  JavaScript ES6. Legacy Apps Script already had destructuring of arrays added fairly recently, but v8 gives full a destructuring capabilitity. These destructuring and spreading capabilities, which at first may again seem a little like syntactic sugar, have contributed greatly to  the development of state management frameworks such as Vuex and Redux for client side apps. V8 brings some of that cleanliness to Apps Script.

Source: Apps Script V8: spreading and destructuring – Desktop liberation

JavaScript V8 Arrow functions, this and that – Desktop liberation

V8 adds the arrow function declarator from modern JavaScript. This is a handy shorthand but it’s more than just that. There are some behavioral differences too that you’ll need to understand before diving in. The old way of declaring functions still exists of course, and there will always be a need for it.

More tips and guidance from Bruce Mcpherson for developers migrating code to the Apps Script V8 runtime.

Source: JavaScript V8 Arrow functions, this and that – Desktop liberation

Apps Script V8: Maps and Sets – Desktop liberation

Sets and Maps can often be a cleaner way of storing data than using Objects or Arrays, even though at first glance they may seem a little redundant. Unlike an array, they are aware of what else is in the map or set (so you can avoid duplicates), and unlike an object, you can use anything as the key – including the item value itself

Given many Apps Script projects focus on manipulating data Bruce Mcpherson provides a useful introduction to Maps and Sets.

Source: Apps Script V8: Maps and Sets – Desktop liberation

Apps Script V8: Template literals – Desktop liberation

It’s a shorthand way of using a template into which variables are subsituted in a string. This allows for better reuse of string structures, and a few other goodies besides (like all V8 additions, it’s more than just a syntactical spruce up)

Bruce Mcpherson has a very useful post highlighting the benefits of using the new V8 runtime when working with string output

Source: Apps Script V8: Template literals – Desktop liberation

Apps Script’s new V8 runtime | Google Cloud Blog

This update to Apps Script lets users take advantage of modern JavaScript features and enhances new or existing scripts with less verbose, more standardized code.

This post from Alexandrina Garcia-Verdin, better known in the community as AGV, highlights some of the benefits of the new Google Apps Script V8 runtime including succinct syntax and data processing operations: map(), filter() and reduce(). As part this AGV also highlights some of the resources from Ben Collins and Amit Agarwal put together to support the community transition to V8. A nice feature of this post from AGV is side-by-side comparisons of old and new JavaScript syntax:

Image source: Google

Source: Apps Script’s new V8 runtime | Google Cloud Blog

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

Subscribe to Apps Script Pulse...