This post introduces a library that can summarize selected columns from multiple sheets across multiple spreadsheets into 1 spreadsheet, as well as to optionally apply a public/private key data encryption scheme to selected columns, all via a simple JSON definition file.
Bruce Mcpherson continues to be one of my favourite Google Apps Script authors and often you get to gain an insight into his development process. This latest post is the latest in a series that follows a post on Super simple cipher library for Apps Script encryption and decryption. The source post shared here continues this journey, in particular, highlighting the use of Google Sheets Developer Metadata to store data bound to the spreadsheet.
This enables you to register Apps Script Release Notes as RSS feed.
RSS data feeds might be less fashionable now, but I for one still rely on them as a way to aggregate and consume latest news. In a conversation with Pablo Felip and Kanshi Tanaike, this solution from Yuki Tanabe for turning the Google Apps Script release notes into an RSS feed was highlighted. You can visit the source link for the RSS link to add to your feed aggregator.
Even if you are not a fan of RSS this project might be worth checking out as the solution implements the cheerio library for parsing/extracting content from HTML markup … or in other words a very simply way to use UrlFetchApp as a web scraper.
🤯 The fact that this was done in Sheets, without Apps Script, blew my mind. Studying the formulas taught me things I never knew, like recursive formulas and using checkboxes to force recalculations. https://t.co/kPIWsQgUEn
Wordle clones are causing a bit of a ruckus lately, so I tried my hand at making one in #GoogleSheets!
Well if you count Google Sheet formulas as code, not quite ‘no code’ but never the less very impressive. You can find more work from the creator Tyler Robertson in this portfolio of work which are all “created entirely within Sheets’s built-in features (meaning there’s no Apps Script or Extensions)”
An Add-on to perform a Google Drive file name search from within a Google Sheet to keep the file information saved for future use.
Search Drive file names in Google Sheets
It is easy to search for the names of files within Google Drive, but what happens when there are a lot of results that you need to go through?! Scrolling continuously down the page becomes quite clunky and frustrating, so with this tool you can perform that same search in a Google Sheet but have the results collated into the Sheet also, so they are much easier to then work with.
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
The team at MacMillan Search has generated a lot of value by combining automated STAT reports with Google Sheets through the script they share in this post. From adding ranking details to other tool’s outputs to giving the content teams up-to-date “People Also Ask” reports, the end result has proven to be a great time-saver in our week-to-week SEO workflows by reducing manual work and providing standard outputs that easily integrate with any spreadsheet.
I always enjoy dipping into content from the SEO community as it has some really creative and innovative solutions. This example from MacMillian Search is a use case I’m familiar with, getting data out of a system as an email report with .CSV attachment and using Google Apps Script to do all the automation to update a Google Sheet. If you are not in SEO still worth a read as the pattern used here may come in handy.
ThinkAmI uses the XMLService of Google Apps Script to create a valid RSS feed that is served to the browser using ContentService with the MIME type set as RSS.
Recently I was looking for a Google Apps Script snippet to create a RSS data feed. Back in the day Apps Script used to have a Xml.parseJS() method which could make a data feed from a JavaScript array. It was announced that the old XML service would be replaced by XmlService in 2013 so we are not talking breaking news here.
The RSS helper script created by ThinkAml and shared by Amit Agarwal makes it a lot easier to generate a feed in a couple of lines of code. To let you see I’ve included a snippet and click through to the source post for the full code:
var rss = makeRss();
rss.setTitle('RSS 2.0 Feed with Google Apps Script');
rss.setLink('http://example.com');
rss.setDescription('RSS 2.0 Feed');
rss.setLanguage('en');
rss.setAtomlink('http://example.com/rss');
for (var i = 1; i < 3; i++) {
rss.addItem({
title: 'TITLE:' + i,
link: 'http://example.com/#' + i,
description: 'DESCRIPTION: ' + i,
pubDate: new Date()
})
}
Take control of your inbox by unsubscribing from emails you never have time to read, or have lost interest in. … I opted to use Google Sheets as a centralized place to store emails and their unsubscribe links to make it as user-friendly as possible. This also provided the added benefit of working with Google Apps Script to extract email metadata from Gmail to Google Sheets.
Interesting little Apps Script project for those interested in tidying up your inbox and also an opportunity to try a bit of python. Alternatively you might want to have a go at modifying this solution to also extract unsubscribe links with Google Apps Script
The following Google Apps Script code is designed to create a HTML popup in a Google Sheet and pass some data directly into it so that the popup can display the information.
Display Apps Script code directly in a HTML popup
This was part of a larger project I was working on so I have stripped all of that away here and kept the bare minimum for demo purposes. I already knew how to get the HTML page to run a Function once it had loaded to then access static data from within the Google Sheet, but what was tripping me up here was if that data was coming directly from Apps Script itself – such as an error message from a try/catch.
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
It’s very convenient to use ScriptApp.getOAuthToken() in an addon to reuse the token from the server side in your client side add-on code. However, these have a limited time to live (1 hour), so what to do if your add on sits around for longer than that? One solution would be to always go for another token every time you needed one, but that would be wasteful. Luckily, there’s a way to check a token to see how much life it has, and only get a new one if it’s almost expired.
There is also a related discussion thread on this post with the author, Bruce Mcpherson, in the Google Apps Script Google Group.
A limitation of the Drive API is that you can only share files with one user at a time. Google Apps Script is synchronous – it doesn’t support the async/await pattern of JavaScript Promises and you therefore cannot run the code in parallel.
There’s however a simple workaround to help you share a file or folder in Google Drive with multiple users in one go in parallel using the UrlFetchApp service.
On Pulse Phil recently shared a snippet for changing a Google Drive file permissions from Editor to Viewer. In this example for Amit Agarwal you can see how you can make these types of changes for multiple users without slowing down your script execution time.
