AppsScriptPulse

Solutions, Sheets, Intermediate

How I Programmed the Game of Life in a Google Sheet with Google Apps Script

Tags:
| | 0 Comments |

According to Wikipedia, the Game of Life “is a cellular automaton devised by the British mathematician John Horton Conway in 1970.”

It begins on a two-dimensional grid of square cells. Each cell can be either alive or dead. Every cell interacts with its eight immediate neighbors. A live cell only remains alive if it has two or three living neighbors. If it has fewer than two living neighbors, it dies as if by underpopulation. Conversely, if it has more than three, it dies as if by overpopulation. A dead cell remains dead unless it has exactly three living neighbors; otherwise, it becomes a live cell, as if by reproduction.

There is no immediate practical use for the Game of Life in a spreadsheet; however, it is a fun algorithmic challenge. Moreover, Google Sheets natively provides us with the perfect data structure: a two-dimensional array. This is all the more reason to work on those array skills!

As usual, there is a GitHub repo with the full source code. Alternatively, you can just make a copy of this spreadsheet.

Source: “How I Programmed the Game of Life in a Google Sheet with Google Apps Script“

Gotchas, Solutions

“This app is blocked” error on Google Apps Script [solution]

Tags:
| | 0 Comments |

In this post, we’ll be going through a quick workaround so that you can get back to running your scripts. Note that this issue is still not entirely resolved, but you can follow any developments in Google’s issue tracker.

If you are a Google Apps Script developer using a consumer @gmail.com account for development/testing or sharing script projects for other users to use with their gmail.com account you may have encountered the “This app is blocked” issue. This issue appears to prevent a Google account from completing the Apps Script authentication flow even when using limited scopes.

This post from Aiman Fikri provides a solution for getting around this issue by associating an Apps Script project to a Google Cloud Platform (GCP) project. Google also provide documentation on setting up Standard Cloud Platform projects, but if you are supporting novice users directing them to Aiman’s post might be less daunting for them.

There are some benefits of using Standard GCP project particularly when you are developing scripts as it gives access to Cloud logs and Error Reporting. If you encounter “This app is blocked” on all your script projects you can group multiple scripts with a single Cloud Platform project to save having to go through the full setup process.

Source: “This app is blocked” error on Google Apps Script [solution]

Official News, Solutions, Chat

Create Spaces and add members with the Google Chat API (and introducing the Google Workspace Developer Preview program)

Tags:
| | 0 Comments |

Image credit: Google

In Google Chat, Spaces serve as a central place for team collaboration—instead of starting an email chain or scheduling a meeting, teams can move conversations and collaboration into a space, giving everybody the ability to stay connected, reference team or project info and revisit work asynchronously.

We are pleased to announce that you can programmatically create new Spaces and add members on behalf of users, through the Google Workspace Developer Preview Program via the Google Chat API.

Besides the new ability to programmatically create and populate Google Chat spaces outlined in the source post, if you are a member of the  Google Cloud Partner Advantage program you may want to apply for the new Google Workspace Developer Preview Program. This program will give you access to this new Chat API functionality and other Google Workspace Developer preview developments.

Not a Cloud Partner Advantage member? You can apply to join

If you are interested in building on the Google Chat platform there is the What’s new in the world of Google Chat apps session at Google I/O (this session will be available on-demand from 12 May 2022).

Source: Now in Developer Preview: Create Spaces and Add Members with the Google Chat API

Snippet, Beginner, Drive, Sheets

Bulk convert Excel files to Google Sheet files

Tags:
| | 0 Comments |

Iterate through a Google Drive folder of Microsoft Excel files and convert them into individual Google Sheet files.

Bulk convert Excel files to Google Sheets

Bulk convert Excel files to Google Sheets

The following Google Apps Script is designed to iterate through a Google Drive folder of Microsoft Excel files and convert them all to individual Google Sheet files.

Source: The Gift of Script: Bulk convert Excel files to Google Sheet files

Comment, Solutions, Sheets, Beginner

Extract and visualize your own Twitter data using Google Apps Script and Google Sheets

Tags:
| | 0 Comments |

Use Apps Script to normalize your tweet data into tabular format for easy visualization & analysis.

I’ve a personal interest in Twitter data, in particular, how it can be collected analysed in Google Sheets so it was nice to see this example from Nick Young (@techupover). The solution shared by Nick uses Google Apps Script to parse a downloaded archive from a Twitter account and write it to a Google Sheet. A nice weekend project if you are looking for something to do :)

Source: Extract & visualize your own Twitter data using Google Apps Script & Google Sheets

Solutions, Drive, Sheets, V8, Intermediate

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

Tags:
| | 0 Comments |

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

Gotchas, V8, Intermediate, Sheets

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

Tags:
| | 0 Comments |

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

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

Snippet, Beginner, Drive

Email notification of Drive file changes within last x hours

Tags:
| | 0 Comments |

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.

Source: The Gift of Script: Email notification of Drive file changes within last x hours