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.
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…
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.
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.
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.
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.
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.
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 Sheetshere.
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.
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.
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.
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.
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.
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.
Screenshot of instructions from Google Sheet with boxes to enter information.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog: www.pbainbridge.co.uk
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.
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.
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
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.