Fortunately, Kanshi Tanaike has been exploring the impact the increased volume of data in Google Sheets has when using Google Apps Script and both SpreadsheetApp and Sheets API. The linked report contains a number of useful findings and strategies for handling large Google Sheets with Apps Script.
Behind the scenes look at what went into creating an Apps Script-native Add-on to generate Webhooks for Google Sheets. ICYMI: You can access the add-on from this link and know more about what it does here
It’s worth checking out Sourabh Choraria’s latest Google Sheets add-on which enables users to quickly setup a Google Sheet to receive data from other services which support the creation of webhooks. As part of this solution the add-on makes extensive use of the Apps Script API, which can be used to programmatically manage Apps Script projects including deployments. This post from Sourabh highlights some of the key endpoints used in the Apps Script API as well as a number of lessons learned about deploying web apps for users.
One of the benefits of Google moving Apps Script to the V8 engine is the possibility to drop in existing JavaScript libraries. Max Makhrov recently highlighted on Twitter how JSONata, which can be used to query and transform JSON data can be used in Google Apps Script:
JSONata is a lightweight query and transformation language for JSON data. Inspired by the ‘location path’ semantics of XPath 3.1, it allows sophisticated queries to be expressed in a compact and intuitive notation. A rich complement of built in operators and functions is provided for manipulating and combining extracted data, and the results of queries can be formatted into any JSON output structure using familiar JSON object and array syntax. Coupled with the facility to create user defined functions, advanced expressions can be built to tackle any JSON query and transformation task. – JSONata
JSONata is a solution better understood by trying it out, which you can do thanks to the script project shared by Max on Twitter or on the JSONata website. If you are interested in data query/manipulation solutions for Google Apps Script you might also want to check out our previous post on using AlaSQL for Apps Script.
If you run and maintain an Open Source project you’ll typically will want to keep track of things like your downloads, stars, commits over time, etc. to help you gauge engagement and overall health of your project. Here’s a quick way hack to keep track of some of this data in Google Sheet which will help you simplify the collection of data and help you better understand what that data means for your project.
This post was picked up by the official @WorkspaceDevs Twitter account, which has been highlighting and amplifying more community contributions recently (hint: you may want to follow/tag the account :)
The two things that caught my eye in this post. First, was the pattern used to get stats from multiple GitHub repos which are all appended in the same row (repo 1 cols B-F and repo 2 cols G-H).
Image credit: Codenotary
As a bit of a REST API/auth geek the second thing that interested me was the use of the Acceptheader, used by GitHub to specify the REST API version being called in UrlFetchApp.
Periodically check for any changes in a given Google Drive file and receive an email notification if there are any, within your given time-frame.
Email notification of Drive file changes. Image credit: Phil Bainbridge
The following Google Apps Script is designed to periodically check for any changes that have occurred to a given Google Drive file (ie edits to it) and send an email to notify of that. It goes through the Revision (Version History) of the file and looks at the modification dates for those that match with your given timeframe, to then collect the Username & Email address of the person that made those edits for inclusion in the email.
So as an example you may have a Google Sheet where you want to setup a check every 4 hours, to then be emailed if there have been any edits to it, along with who made those edits.
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, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
In this post we’re going to look at how we can get data from an Excel spreadsheet and import some of it into a Google Sheet. As an example, we’re going to upload an Excel which contains the current month’s royalty data from Amazon and add it to a central sheet which contains all the previous month’s royalties.
Similar to the post on converting a PDF document to text, this tutorial from Baz Roberts uses a similar approach of using the Google Drive API for file conversion, this time converting a MS Excel file into Google Sheets to make it possible to easily extract and update a master spreadsheet. The post includes a detailed explanation of the shared code.
This tutorial explains how you can parse and extract text elements from invoices, expense receipts and other PDF documents with the help of Apps Script.
We’ve previously featured a method for extracting text from a PDF from Scott Donald. This latest post from Amit Agarwal uses a similar technique of sending a PDF document to Google Drive API to convert to a text file and then using RegEx to extract the content you need. Given the number of PDF documents flying around between organisations both of these posts from Amit and Scott are worth being aware of for potential future projects.
In this article, I will show you how can you visualize google calendar events in the google maps using Google Calendar, Maps Javascript API, HTML, CSS and Google Apps Script.
This is an interesting project shared by Kevin Vaghasiya which uses Google Apps Script to create a web app to display a range of Google Calendar events on a map. On the backend the script is using the Maps Service and geocode(address) to get the co-ordinates of the event location. This could be an interesting project to modify to send yourself a daily email with a static map image of your appointments.
Learn about the different approaches that will help insert images in Google Sheets and understand the reason why you may prefer one approach over the other.
In this tutorial Amit Agarwal covers the various ways you can insert images into Google Sheets, including with Google Apps Script. As part of this you can learn about the CellImage and CellImageBuilder which are relatively new features for adding an image to a Google Sheets cell. There are some useful code snippets included in the tutorial that can be dropped into your own projects.
Convert array values from the doGet & doPost event parameters into 2D array when writing them onto a Google Sheet.
Sourabh Choraria has hit a rich patch of Google Apps Script exploration and following the post we shared recently on Google Sheets cell precedents in this latest post Sourabh looks at restructuring data into a cartesian product. The post contains some nice other ‘Easter Eggs’, in particular, worth checking out Sourabh’s latest Workspace Add-on, Webhooks for Sheets, and a snippet of code on GitHub used in the add-on for handling GET and POST requests.
