AppsScriptPulse

How to Auto-Download Podcasts to Google Drive with Google Sheets [and Google Apps Script] – Digital Inspiration

You use Google Sheets as your own Podcast Manager that will automatically download your favorite podcasts to Google Drive and instantly sync across all your devices.

Amit Agarwal is always coming up with creative uses for Google Apps Script. His latest project is a great example of what can be achieved with a little code and a lot of know-how. Even if you aren’t a podcast fan this project is worth checking out as Amit is expert at writing concise and efficient code.

Some highlights to check out once you make a copy of the ‘Podcast Manager’ Google Sheet are use of CacheService for getting/putting a last update time and using the .filter(Boolean) trick for ignoring blank cells when using .getValues() on Google Sheets data. There is plenty more going on and worth spending some time using the Script Editor debugger and breakpoints to learn from a master.

Source: How to Auto-Download Podcasts to Google Drive with Google Sheets – Digital Inspiration

Report: Handling 10 million cells in Google Sheets using Google Apps Script

In this report, I would like to introduce the important points for handling 10,000,000 cells in Google Spreadsheet using Google Apps Script.

In March 2022 Google announced that the Google Sheets cell limit is doubled from 5 million to 10 million cells. The increased capacity has implications for Google Workspace developers as you now may encounter scenarios where you have users with lots of data.

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.

Source: Report: Handling 10,000,000 cells in Google Spreadsheet using Google Apps Script

JSONata – JSON query and transformation language in Google Apps Script

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 

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

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

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

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

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

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

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

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

Retrieving Google Docs summaries using Google Apps Script (hint: it’s using the existing Google Drive description property)

This is a sample script for retrieving the summary of Google Document using Google Apps Script. Recently, a blog of Auto-generated Summaries in Google Docs has been posted. I thought that this is very interesting function. I thought that when this function is released, checking each summary of a lot of Google Document will be much useful for simply confirming the document content. And also, I thought that when all summaries can be retrieved using a script, it will be also useful. In this post, I would like to introduce to retrieve the summary of Google Document using Google Apps Script.

There are a couple of pieces of interesting information highlighted by this post by Kanshi Tanaike that caught my eye . First, useful to have a reminder that Google announced automatically generated summaries in Docs in February 2022. This might be a premium feature for the paid Workspace accounts as like Kanshi I’m not seeing this yet in my own free Google Workspace domain. The bit that really caught my eye is Google Docs summaries are using the existing Google Drive description property, which means it is not currently available via DocumentApp or if using the Google Docs API as an Advanced Service Docs . I’m sure this will catch some people out and hopefully this post will point people in the right direction.

Source: Retrieving Summary of Google Document using Google Apps Script

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

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

Find precedents of cells with formulas in Google Sheets using Apps Script — part 1

enumerate a range in google sheets using google apps script and use that to find precedents of cells that contain a formula.

Let’s start with the ubiquity of spreadsheets. I’ve long felt that spreadsheets were everywhere but until a post from Hjalmar Gislason I had never seen it in numbers and it made for interesting reading:

  • 1.2 billion people use Microsoft Office (WindowsCentral, March 2016), odds are most of them have at least access to Microsoft Excel.
  • Microsoft believes that 1 in 5 adults in the world use Excel (“What’s new in Microsoft Excel”, Sept 2017)
  • Excel is the number one skill mentioned in job ads, mentioned in approximately 1 in 3 job ads! (Indeed.com Job Trends, June 2017).
  • In 2010, RescueTime found that about 25% of computer users used Excel on a daily basis and that about 2% of all time spent on a computer anywhere was spent using Microsoft Excel.

As useful as spreadsheets are the underlying data structure can cause problems. This is covered in more detail in Patrick Burns’ Spreadsheet Addiction, which amongst other things highlights the ambiguity of value and formula:

Primarily the problem is that some cells have hidden meaning. When you see a number in a cell, you don’t know if that is a pure number or a number that is derived from a formula in the cell. While this distinction is usually immaterial, it can be critical.

The leading example is sorting. When rows are sorted, usually it is desired to sort the numbers as they are. However, it will be the formulas that are sorted. To humans the numbers are the primary thing, and the formulas are just something in the background. To the spreadsheet the formulas are primary, and the numbers are mere results.

This is a long winded way to highlight some work from Sourabh Choraria which is exploring how the relationship between formulas and cells can be extracted in Google Sheets. As part of this Sourabh is looking at how the getFormulasR1C1 method in SpreadsheetApp can be processed to find precedent cells. There are some very funky use of regex to parse formulas which is worth spending some time to digest.

Source: find precedents of cells with formulas in google sheets using apps script — part 1

Subscribe to Apps Script Pulse...