AppsScriptPulse

How to send text messages using Google Sheets and Twilio – Sheets to Apps

Did you know that you can send text messages via Google Sheets? In this episode of Sheets to Apps, we show you how to easily send text messages with Google Sheets via Twilio – an online communications provider.

Back in January Sourabh Choraria highlighted a Medium post from AVG showing how to Send SMS event updates or discount codes using Twilio from a Google Sheet. AVG has now created a short explanatory video covering this solution. You can view the video description for all the code and resources you need to try out this solution.

Send Rich Text HTML Emails with Google Sheet – Digital Inspiration

You can format the source data in Google Spreadsheet in different colors, fonts and sizes and all your cell formatting will be retained in the email messages.

Very handy code snippet from Amit Agarwal which lets you convert the formatted text in a Google Sheets cell to HTML. In the example Amit uses the formatted text in an email sent with MailApp but you might find other uses for this solution. Click through to the source link for all the details…

Source: How to Preserve Formatting of Spreadsheet Cells in Mail Merge – Digital Inspiration

Dashboards in your inbox – Revisting tips on emailing inline Google Sheet chart images with Google Apps Script

Back in 2015 I shared a post on Tips on emailing inline Google Charts from Sheets using Apps Script, which looked at how you can email charts from Google Sheets. This solution is one I use in my work on a daily basis and remains largely unchanged. More recently I was faced with the problem that I couldn’t include a Combo Chart. In this post I revisit this problem and share a solution for including copies of your Google Sheet charts in your email reports.

Whilst this solution focuses on extract Google Sheets chart images for the purpose of emailing, the technique might be of interest for other situations where you need to use a chart image.

Source: Dashboards in your inbox – Revisting tips on emailing inline Google Sheet chart images with Google Apps Script

Workbook Statistics – a Google Sheets Add-on built using Google Apps Script (lessons in Add-on publication)

Get stats for your current active sheet & the entire spreadsheet in one place using workbook statistics – a g suite editor add-on built using google apps script.

A handy Google Sheets add-on from Sourabh Choraria that can give you an Excel like summary of the Google Sheet you are working on that can display information like the number of: filled cells, formulas, charts and more. This post is also worth reading if you are interested in tips for the Add-on publication processes, highlighting some of the pitfalls you can avoid. The code for the Add-on is also open source making it possible to reuse in your own Apps Script projects.

Source: workbook statistics – a google sheets add-on built using apps script

Converting Range in Google Spreadsheet as Image using Google Apps Script · tanaike

This is a sample script for converting a range in Google Spreadsheet as an image data using Google Apps Script. Unfortunately, there are no methods for directly converting the range in Google Spreadsheet as an image data in the built-in functions. So in this case, as a workaround, Charts Service is used.

Source: Converting Range in Google Spreadsheet as Image using Google Apps Script · tanaike

Measure Core Web Vitals of your Websites with Google Sheets – Digital Inspiration

Core Web Vitals are a set of metrics defined by Google to help webmasters understand the performance of their websites. You can automate the measurement and tracking of core vitals with Google Sheets.

Another incredibly rich resource from Amit Agarwal containing useful guidance on not only performance of websites and potential impact on search ranking, but also a very concise coding pattern for reading and writing data in Google Sheets.

Source: Measure Core Web Vitals of your Websites with Google Sheets – Digital Inspiration

Google Maps Formulas for Google Sheets – Digital Inspiration

You can bring the power of Google Maps to your Google Sheets using simple formulas with no coding. You don’t need to sign-up for the Google Maps API and all results from Google Maps are cached in the sheet so you are unlikely to hit any quota limits.

Great post from Amit Agarwal using the Maps Service for Google Sheets custom functions.

Use Google Maps formulas inside Google Sheets to calculate distances, travel time, get driving directions, look up postal codes with reverse geocoding and more!

The post includes all the source code and has some great examples using the V8 syntax.

Source: Google Maps Formulas for Google Sheets – Digital Inspiration

Build Apps Powered by Language with Semantic ML with the new Semantic Reactor Google Sheets Add-on

In this post, I’ll show you how to use beginner-friendly ML tools–Semantic Reactor and TensorFlow.js–to build an app that’s powered by natural language.

NEW: Semantic Reactor has been officially released! Add it to Google Sheets here.

We’ve previously highlighted how TensorFlow.js can be used in Google Apps Script. In this latest example the new Semantic Reactor Google Sheets Add-on published by Google Research is highlighted. Semantic Reactor is designed to make it easier to start exploring Natural Language Understanding (NLU) and with this tutorial you can learn how you can deploy your model to code.

Source: Build Apps Powered by Language with Semantic ML

Google Apps Script: Automated PDF Certificate Creator That Is Emailed to the Attendee – Yagisanatode

Use Google Apps Script, Google Slides and Google Sheets to quickly automate creating a Certificate of Attendance and send it to attendees.

Nice example using Google Apps Script to generate and share .pdf documents via email. The source has all the code you need to get going on this.

Source: Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee – Yagisanatode

Pen-based Interaction with Google Sheets in Mobile Virtual Reality

I’m sure for many pro Google Sheets users when you look at a Google Sheet what you see on screen is different for what you ‘see’ in your mind. Researchers from Coburg University, Microsoft Research and the University of Cambridge have potentially bridged this gap showing what Google Sheets can look like virtual reality (VR).

Whilst the video embedded above is not explicitly about Google Apps Script, I think it’s always worth keeping an eye on the horizon to see what the future might bring (you might want to skip through to 05:12 to see what I mean). You can also read the full research paper, in which the researchers explain how they used the Google Sheets API to build the experimental interface in the Unity game engine.

For Google Apps Script developers I’m sure you’ll be able to see how this solution could also benefited from the built-in SpreadsheetApp methods like getCurrentCell() and activate(), rather than using the the Sheet API, which reported as a limitation that haas reported in the research paper:

Since the Google Sheets API exposes no functionality to track client-side interactions, operations of users with the web page were tracked inside Unity. In particular, tracking of cell selection was implemented by constructing virtual-cells in the Unity space using oriented bounding boxes, and spatially position them in their corresponding places to fit the spreadsheet texture. Tracking of the pen and the Unity collision detection mechanism is used to detect whether the pen tip lies inside a certain cell.

Gesslein et al. (2020)

How to build a Google Analytics Dashboard with Apps Script and Google Sites – Sheets to Apps

Using Google Analytics and looking for a new way to aggregate, store, and efficiently organize the data from your website in a Google Sheet? In this episode of Sheets to Apps, we show you how to combine the Google Analytics add-on with Apps Script, resulting in the automation of cohesive data summaries that are compatible with Google Sites.

In this latest episode Alexandrina Garcia-Verdin demonstrates how the Google Analytics Add-on can be combined with macros to automate regular reporting. The video description has links to all the resources you need to set this up.

Whilst ‘Sheets to Apps’ videos are targeted at low/no coders as an Apps Script Developer I always find it useful to have a look at the code to see how it is done, as more often than not there will be a technique or method I’ve not come across. In the case of this example my new discovery was the .autoFill() and after browsing the documentation, .autoFillToNeighbor() methods in SpreadsheetApp. These methods allow you to replicate the auto-fill functionality users have in Google Sheets.

Another discovery was the various .setOption() calls when building the charts. I’ve used .setOption() in projects in the past and the issue I’ve had is navigating the long list of options you can use for various chart types. Using the macro recorder seems like a great way to capture all the options you want to add to your script project.

How To Create An Android App That Posts Pictures to Google Sheets with Google Apps Script

I used the MIT App Inventor site to create an Android app that…
  • Uses the camera to take a picture and saves it to the device’s gallery
  • Let’s you select a picture from the gallery
  • The image is converted into base64 and uploaded to a web app
  • Additionally, it also sends rotation and lat/lng coordinates and lets you add a textual comment as well.
This Android app sends it to an Apps Script web application inside a Google Spreadsheet

Nice fun project from Tom Smith that use MIT App Inventor to create an Android app that is able to send data to a Google Sheet. Check out the source link for all the code you need.

Source: How To Create An Android App That Posts Pictures To Google Sheets

Build A Lookup Table Generator For Google Tag Manager with Google Sheets and Apps Script

The purpose of the Lookup Table generator is to automate the often tedious task of adding many, many rows to a Lookup Table within the Google Tag Manager UI. There are other solutions for this, but none (as far as I know) that uses the Google Tag Manager API.

Within the Google Apps Script community there is a strong representation from those working in analytics and SEO. Simo Ahava has shared a comprehensive tutorial which shows how you can interact with the Google Tag Manager API in Google Sheets. Click through to the source for more details and all the code you need.

Source: Build A Lookup Table Generator For Google Tag Manager | Simo Ahava’s blog

Send Tweets from Google Sheets using a Google Apps Script

In this post, you will learn how to send automated tweets from Google Sheets to Twitter using Google Apps Script.

This post includes everything you need to get started sending tweets for Google Sheets. This tutorial uses the community contributed Twitterlib library from Bradley Momberger, which has some implemented methods for sending and fetching tweets.

Source: Send Tweets from Google Sheets using a Google Apps Script

Bulk export Qualtrics survey results to Google Sheets using Google Apps Script

The following tool is designed to connect to your Qualtrics account and export the results data for multiple surveys that you specify, in one go. The results will be appended to the Google Sheet using the survey-name to identify each sheet of data. The data itself is exported as CSV.

The spreadsheet requests some details to connect to your qualtrics accounts with clear instructions on how.

Screenshot of instructions from Google Sheet with boxes to enter information.

 

Source: The Gift of Script: Bulk export Qualtrics survey results

Sync RingCentral Call Log into Google Sheets with Google Apps Script | by Embbnux Ji | RingCentral Developers | Medium

In this article, I would like to show you to how to create a Google Sheets add-on to help people sync RingCentral call log into spreadsheets.

Nice post highlighting how you can integrate the RingCentral service into a Sheets Add-on. The post includes more general information about setting up access to the RingCentral API which might be useful in your other projects.

Source: Sync RingCentral Call Log into Google Sheets with Google Apps Script | by Embbnux Ji | RingCentral Developers | Medium

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet – Yagisanatode

Have you ever asked for a list from a client, another department or agency and instead of saving into a file that might even be considered marginally useful, they give it to you as a pdf. … Continue reading “Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet”

Yagi shares a comprehensive walk-through of how you can sometimes extract data from .PDF documents using Google Apps Script and the advance Drive service. The solution is also able to work on .PDFs created from images using the built-in OCR features of Google Drive and you might want to drop the getTextFromPDF() function shared in the post.

Source: Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet – Yagisanatode

How macros can be used to build Apps Scripts for cleaning data – Sheets to Apps

Cleaning up data from downloaded accounting reports or from other systems can take up valuable time and resources. In this episode of Sheets to Apps, we’ll walk you through adding automation to your monthly Excel accounting data that records your steps into a script that you can run repeatedly with Google Sheets macro recorder.

From the video description you’ll find all the links you need to the example Google Sheet, instructions and more

Calculating moving averages in Google Sheets with Google Apps Script (and tips on writing custom functions)

Por esa razón me he puesto manos a la obra y he preparado MEDIAMOVIL(), una función personalizada GAS capaz de calcular varios tipos de medias móviles, que puede ser utilizada (como todas las funciones personalizadas) de manera combinada con el resto de funciones integradas que nos ofrecen las hojas de cálculo de Google.

TRANSLATED: For this reason I have gotten to work and have prepared MEDIAMOVIL(), a custom GAS function capable of calculating various types of moving averages, which can be used (like all custom functions) in combination with the rest of the integrated functions. offered by Google spreadsheets.

Great post from Pablo Felip (@pfelipm) on calculating moving averages in Google Sheets. As part of this Pablo has developed and shared the MEDIAMOVIL() custom function he has developed in Google Apps Script. Clicking through to the source code on Github Pablo has also shared lots of advice on developing custom functions for Google Sheets including using the contextual help features available in custom functions as well as handling parameters and throwing appropriate error messages to the user.

Source: Calculando medias móviles con Google Apps Script

Subscribe to Apps Script Pulse...