AppsScriptPulse

Wordle meets Google Sheets in SHEETLE (a ‘no code’ solution)

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)”

Search Drive File Names Add-on

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

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.

This Google Sheet will contain:

  • a clickable link to the file,
  • the file type,
  • the date it was created,
  • when it was last updated,
  • the folder path where it is stored,
  • the ID of the file.

Source: The Gift of Script: Search Drive File Names Add-on

Adding STAT Reports [extracting .csv email attachments] to Google Sheets Using App Scripts

Image credit: moz.com

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.

Source: Adding STAT Reports to Google Sheets Using App Scripts

Want to become a recognised Google Developer Expert in Google Workspace/Apps Script? – About Road to GDE

Image credit: Google

“Road to GDE” is a 3-month mentoring program focused on supporting people from historically underrepresented groups in tech on their path to becoming a Google Developer Expert.

This site is hopefully proof that there is an incredibly rich Google Apps Script / Google Workspace developer community. If you are one of those contributors based in EMEA, LATAM and India from an underrepresented group ‘Road to GDE’ is a great opportunity to help you become recognised as a Google Developer Expert in the field that you are already passionate about. The source link contains more information about requirements and how to start your ‘Road to GDE’

Source: Road to GDE – About Road to GDE

Make an RSS Feed with Google Apps Script – Digital Inspiration

RSS flickr photo by Lars Plougmann shared under a Creative Commons (BY-SA) license

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()
})
}

Source: Make an RSS Feed with Google Apps Script – Digital Inspiration

Extract Unsubscribe Links from Emails using Apps Script and Python

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

Source: Extract Unsubscribe Links from Emails using Apps Script and Python

How to pass Apps Script data directly into a HTML page

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.

Source: The Gift of Script: How to pass Apps Script data directly into a HTML page

Trend of the google-apps-script tag on Stack Overflow for 2021. Apps Script usage in decline?

Stack Overflow is a question and answer website for professional and enthusiast programmers. It is the flagship site of the Stack Exchange Network, created in 2008 by Jeff Atwood and Joel Spolsky. It features questions and answers on a wide range of topics in computer programming.

Wikipedia

Kanshi Tanaike has recently published the 4th annual Stack Overflow trend report for the Google Apps Script question tag. The full report, including how the data is collected, is published here and there is some further discussion on the Google Apps Script Google Group community.

From the headline data it is interesting to see the total number of Google Apps Script questions posted on Stack Overflow has declined following previous years of continued growth. It’s important to remember that this decline is unlikely to directly correlated with general App Script usage. Factors that might be influencing the number of questions asked include like better developer documentation, larger corpus of existing Stack Overflow answers, growing number of other community resources and spaces.

Fig. 1. Year vs. Total questions, answered, solved and closed questions. These all questions include the tag of “google-apps-script” in the tags. Image credit: Kanshi Tanaike

Another consideration is Google Apps Script is increasingly positioned as a platform for citizen developers and low coders. This community may be more hesitant to ask questions on a site that has a reputation as being for ‘professional programmers’. My conclusion is the annual Stack Overflow report for Google Apps Script is always very useful to see, particularly in terms of see if there are enough people answering questions.

Finally, as a little easter egg here is a Google Sheet I’ve created which includes a little Apps Script to fetch my own Stack Overflow annual report data. Copy and open the script editor to add a monthly trigger to get the data. Next year I’m planning to look at changes in post viewcount :)

Refreshing an oauth token in a Google Workspace Add-on with Google Apps Script – Desktop Liberation

Image credit: Igor Ovsyannykov, CC0, via Wikimedia Commons

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.

Source: Refreshing an oauth token in add-on – Desktop Liberation

How to Share Files in Google Drive with Multiple Users using Google Apps Script – Digital Inspiration

Google Drive Access Screen

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.

Source: How to Share Files in Google Drive with Multiple Users – Digital Inspiration

Subscribe to Apps Script Pulse...