AppsScriptPulse

Library, Solutions, V8, Intermediate

JSONata – JSON query and transformation language in Google Apps Script

Tags:
| | 0 Comments |

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.

Update: Max has published JSONata as an Apps Script library 

Solutions, Sheets, Intermediate

Use Google Sheets Apps Script to track Open Source GitHub and Docker statistics

Tags:
| | 0 Comments |

Image credit: Codenotary

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 Accept header, used by GitHub to specify the REST API version being called in UrlFetchApp.

Source: Use Google Sheets Apps Script to track Open Source GitHub and Docker statistics

Solutions, Drive, Sheets, V8, Intermediate

Importing data from MS Excel to Google Sheet with Google Apps Script

Tags:
| | 0 Comments |

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.

Source: Importing Amazon royalty data from Excel to Google Sheet – Learning Google Workspace & Apps Script

Solutions, Drive, V8, Intermediate

How to extract text from PDF files with Google Apps Script – Digital Inspiration

Tags:
| | 0 Comments |

Image credit: Amit Agarwal

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.

Source: How to Extract Text from PDF Files with Google Apps Script – Digital Inspiration

Solutions, Calendar, Maps, V8, Intermediate

See your Google Calendar events in Google Maps using Google Apps Script

Tags:
| | 0 Comments |

Image credit: KEVIN VAGHASIYA

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.

Source: See your calendar events in Google Maps using Google Apps Script

Snippet, Solutions, Sheets, V8, Intermediate

How to insert images in Google Sheet cells [with built-in functions and Google Apps Script] – Digital Inspiration

Tags:
| | 0 Comments |

Image credit: Amit Agarwal (@labnol)

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.

Source: How to Insert Images in Google Sheet Cells – Digital Inspiration

Solutions, Web App, V8, Intermediate

Unnest an object with array values using cartesian product [for Google Apps Script POST/GET web apps]

Tags:
| | 0 Comments |

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.

Source: Unnest an object with array values using cartesian product

Solutions, Sheets, V8, Intermediate

Use a Google Sheets as a database in Google Apps Script without sharing it

Tags:
| | 0 Comments |

Make Google Sheets as your database for your web app or add-on developed in Apps Script without sharing the file. … In order to keep our database secret to end users we will use a Service Account (SA) … This Service Account is not a real account, no Gmail or Drive attached but a service account can access a spreadsheet. We will use this property to manage our data.

As the post author Stéphane Giron indicates before using Google Sheets as a database you need to fully scope out if it’s the right solution for you. For ‘small’ data you can often get away with using Sheets and in this post from Stéphane you can learn how to do it in a way where you can have access control by setting up and using a Service Account.

Source: Use a Google Sheets as a database in Apps Script without sharing it

Solutions, Sheets, V8, Intermediate

Using spread syntax and destructuring assignment in Google Sheets .getValues() with Google Apps Script

Tags:
| | 0 Comments |

In this report, I would like to introduce to apply the spread syntax and the destructuring assignment to Google Spreadsheet with Google Apps Script. … I have sometimes gotten the questions related to the spread syntax and the destructuring assignment. So, I thought that I would like to introduce in my blog.

We’ve featured other coding patterns from Kanshi Tanaike and here is some other nice examples of how you can handle 2D arrays returned by .getValues(), in particular, this post shows a nice way of getting non-adjacent columns in Google Sheets.

Source: Applicating Spread Syntax and Destructuring assignment to Google Spreadsheet with Google Apps Script

Solutions, Drive, Sheets, V8, Intermediate

Use Google Apps Script to pull all Google Shared Drives and permissions into Google Sheets

Tags:
| | 2 Comments |

If you’re a Google Workspace Super Admin, you’ve probably been thinking of ways to inventory and keep a handle on Google Shared Drives. They are a really great addition to the Workspace offering, but there are still some gaps in reporting & oversight that some people have wanted. This solution uses Google Apps Script and the Drive API to pull information about all the Shared Drives in a domain into a Google Sheet. Then, it uses the Drive API again to loop through all the drives and get the top-level permissions.

For Google Workspace admins Nick Young has provided a nice solution for auditing Google Shared Drives within your Workspace domain. This post covers all the steps to setup, a link to the code on GitHub and some notes on limitations to be aware of.

Source: Use Apps Script to pull all Google Shared Drives and permissions into a Google Sheet