AppsScriptPulse

TeslaGAS is a Google Apps Script library that helps writing scripts for your Tesla car

Photo by Afif Kusuma on Unsplash

Photo by Afif Kusuma on Unsplash

TeslaGAS is a library that helps writing scripts that communicate with your Tesla – Zzapps/teslagas

Riël Notermans from Zzapps has published the beginnings of a nice little Google Apps Script library that you can use to communicate with your Tesla car.

Source: Zzapps/teslagas

Unit Testing in GAS Part 5: Testing Objects and Arrays

It’s time to dive into deeper equality situations with objects and arrays. Every test we’ve written so far has used a non-strict comparison. In other words, we’ve only been checking value but not type. This is particularly important in JavaScript because of how it handles truthy and falsy values.

The fifth part of a series using the GAS QUnit library to write unit tests for Apps Script projects in the web editor. This post details testing arrays and objects.

Source: Unit Testing in GAS Part 5: Testing Objects and Arrays

Bulk add unique data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk insert a Google Sheet of data into an existing SQL Table, but only after checking the record does not already exist so that it can be safely re-run.

Source: The Gift of Script: Bulk add unique data to an SQL Table in Apps Script

Email scheduler for Gmail – overview

Email scheduler spreadsheet columns image

Email scheduler spreadsheet columns

Over the next few blog posts I look to explore my development of an email scheduler for Gmail that is designed to let you schedule when you want an email composed in your drafts to be sent (by specifying the date/time).

This was written prior to Google introducing an email scheduler themselves.

Source: The Gift of Script: Email scheduler for Gmail – overview

Automating Academic Reviewer Finding With Microsoft Academic and Google Apps Script

One of the big problems today is finding reviewers. Editors are limited by their own knowledge and various publicly-available datasets (such as Google Scholar, Web of Science, and discipline-specific tools like philpapers.org or thephilosophypaperboy.com), and while especially these latter are useful, they are not tailor-made for reviewer finding. This setup presents a way to speed up peer-review by partially automating finding reviewers for academic papers.

The aim here is to make something that is tailor-made for reviewer finding, that works by taking data from one publicly available dataset (namely that of Microsoft Academic, which is basically Google Scholar but Microsoft), extracting from it a list of possible reviewers, creating a database from that, and letting one query this newly created database to find reviewers for a paper under your editorship.

Source: Automating Academic Reviewer Finding With Microsoft Academic and Google Apps Script

Loop delete specific rows with counter

Example table with specific rows to delete

Example table with specific rows to delete

Table with row 3 now deleted and data shifted up

Table with row 3 now deleted and data shifted up

The following Google Apps Script has been created to help with a recent difficulty I was having with deleting rows containing a specific value, where the loop was losing the row position due to the spreadsheet data all shifting up a row each time one was deleted. Some searching online suggested looping through a spreadsheet in reverse (from bottom up) to avoid the shift, but that seems more like a workaround than a direct solution …

Source: The Gift of Script: Loop delete specific rows with counter

Data Validation – check for blank cells

The following Google Apps Script code was developed as a tool to check that all the necessary data existed in a spreadsheet before another function was triggered. The reason for this was that if any data was missing it would affect the student Group files I was creating (eg no email address or name) that would eventually prevent further functions from running at a later date.

Source: The Gift of Script: Data Validation – check for blank cells

Google Apps Script snippet for Google Sheets to view json strings in a modal dialog

View json strings in a modal dialog

View json strings in a modal dialog. Activate the cell that contains a JSON string Click menu item A foldable JSON will be shown in the modal dialog

Handy little Google Apps Script snippet from Riël Notermans (Zzapps) that makes it easier to view any JSON you are storing in a Google Sheets cell.

Source: Zzapps/google_sheets_json_viewer

5 Google Sheets Script Functions You Need to Know

Google Apps scripting is a background scripting tool that works not only in Google Sheets but also Google Docs, Gmail, Google Analytics, and nearly every other Google cloud service. It lets you automate those individual apps, and integrate each of those apps with each other.

Source: 5 Google Sheets Script Functions You Need to Know

Google Apps Script Development – Best Practices – Andrew Roberts

This is an overview of the various techniques and best practices I have evolved in developing Google Apps Scripts over the years. Of course Google have got a few suggestions of their own, and there are plenty of more general …

This includes some good design pattern considerations for Apps Script projects as well as links to testing libraries, boilerplate frameworks, and extensions that help manage Apps Script work.

Source: Google Apps Script Development – Best Practices – Andrew Roberts

iterate ~80x faster through spreadsheet using map function in apps script

use array map method instead of “for” loops to iterate faster through 2-dimensional spreadsheet data.

Sourabh Choraria shares his experience of using Array.map to find values in a Google Sheet and the performance benefit of using this approach.

Source: iterate ~80x faster through spreadsheet using map function in apps script

Bulk save emails from Gmail

Screenshot of setup sheet for tool.

Screenshot of setup sheet for tool.

This tool is designed to bulk save emails (and associated attachments) from a specified Gmail label into a Google Drive folder, with relevant threads combined into a single PDF document.

Source: The Gift of Script: Bulk save emails from Gmail – overview

Log actions performed by a user running a Google Apps Script

Screenshot of spreadsheet with logs from script

Example log output from script to spreadsheet

A standalone function that you could include in your scripting projects to help determine which user ran a script and what actions it has performed. It is designed to output the information into a Google sheet – Date/Time, User, Action.

Source: The Gift of Script: Log actions performed by a user running a script

Convert Doc to PDF and move into a new folder with Google Apps Script

Create a PDF version of a Google Doc, move it into a new folder, remove its parents so it only exists in the new folder (typically also exists in My Drive) and then trash the Doc.

Source: The Gift of Script: Convert Doc to PDF and move into a new folder

Check if a date is more than a month ago

Recently during a consultation I was asked if it would be possible to check if the date submitted on a Google form was over a month ago, to which I responded “sure …”.

Admittedly this was a lot more difficulty than I thought it would (and should) be – I was bamboozled by online forums and posts suggesting to convert dates into numbers and perform other incoherent functions to achieve this. Eventually however I came across the single JavaScript function that I would need to achieve this feat …… ‘getMonth’.

Source: The Gift of Script: Check if a date is more than a month ago

Protect a named range in a Google sheet

The following Google Apps Script code is from some recent learning I have been doing when asked about locking-down certain areas of a sheet. I knew of named ranges and protecting cells but not quite that it could be done with apps script.

Source: The Gift of Script: Protect a named range in a Google sheet

SpreadAPI: add REST API to any spreadsheet in Google Sheets

SpreadAPI

Image: spreadapi.com

SpreadAPI is a free Google Apps Script that allows you to add REST API to any spreadsheet in Google Sheets in a few minutes. The API can be accessed over HTTPS from back-end as well as front-end apps.

It’s currently possible to publish a Google Sheet as JSON to get the data in a third-party application, but it’s harder to allow for posting new data back to that sheet. SpreadAPI is a script that creates a full-function REST API for a Google Sheet. Each implementation is set up individually and provides multiple authentication methods for specific users or even anonymous users.

Source: spreadapi.com

Stripe payment code integrated into Google Apps Script

Stripe Payment Example

Regular Google Apps Script community contributor Alan Wells has provided example Apps Script code with different examples of Stripe payment integration.

Update: Alan had published the source code to GitHub

Source: Stripe payment system – client version v3 – Apps Script integration 

Unit Testing in GAS Part 4: Error Handling | ohhey[blog]

Up until now, our Calcs class has handled errors with simple true and false flags. That’s not helpful to the user. At this point, we’re ready to begin defining and testing custom errors in our functions.

QUnit allows a throws method which can check for errors in your application. This tutorial post adds some error checking to a simple Apps Script project.

Source: Unit Testing in GAS Part 4: Error Handling | ohhey[blog]

Get weekday name from a date

Take a date and extract the day of the week from it. By making use of ‘formatDate’ and the pattern ‘EEEE’ within a few lines of code we have a workable week-day name that could be used in an if statement as discussed in the previous post.

Source: The Gift of Script: Get weekday name from a date – alternative

Subscribe to Apps Script Pulse...