AppsScriptPulse

Solutions, Sheets, No Code

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

Tags:
| | 0 Comments |

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

Solutions, Sheets, Beginner

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

Tags:
| | 0 Comments |

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

Solutions, Intermediate

Make an RSS Feed with Google Apps Script – Digital Inspiration

Tags:
| | 0 Comments |

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

Solutions, Gmail, Sheets, Intermediate

Extract Unsubscribe Links from Emails using Apps Script and Python

Tags:
| | 0 Comments |

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

Snippet, Beginner, Sheets

How to pass Apps Script data directly into a HTML page

Tags:
| | 0 Comments |

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

Solutions, V8, Intermediate

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

Tags:
| | 0 Comments |

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

Solutions, Drive, V8, Intermediate

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

Tags:
| | 0 Comments |

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

Add-on, Solutions, Sheets, No Code

The Gift Of Script: Archive Sheet data Add-on

Tags:
| | 0 Comments |

Move/archive a row of data from one Google Sheet (tab) to another, within the same file, using this Google Workspace Add-on. It will also remember your individual preferences for each file so it is even quicker to use next time around.

New Features
January 2022
A new ‘Settings’ page allowing you to select more than just cell values when archiving row data. You can now also select:
> Text formatting: bold, italics, colour, size and style.
> Cell: background colour, Notes and wrapping.

Source: The Gift of Script: Archive Sheet data Add-on

Snippet, Beginner, Drive

Change file permissions from Editor to Viewer

Tags:
| | 0 Comments |

Google Drive Access Screen

How to change Google Drive file permissions from Editor to Viewer as said user.

The following Google Apps Script is designed to change the Google Drive file permissions of a specific user from Editor to Viewer. The function actually came about when needing to end a process where a user had been editing a Google Sheet on a Shared drive that they should only then have Viewer access to. To complicate matters the Apps Script code is running as said user when it needs to reduce their own permissions.

Source: The Gift of Script: Change file permissions from Editor to Viewer

Solutions, V8, Advanced

The ULTIMATE Guide to NPM Modules in Google Apps Script

Tags:
| | 0 Comments |

The What and Why of NPM Modules

Google Apps Script has some amazing built-in stuff. It gives us native access to all Google apps like Sheets and Gmail, seamlessly integrates with GCP services like BigQuery, allows for the building of interfaces with HTML and CardService, facilitates the creation of simple webhooks/APIs and web apps with simple and efficient client-server communication, can make use of any API through UrlFetchApp, and can be bundled into add-ons for efficient distribution. In my experience, it’s enough for 99% of all Google Apps Script developers.

However, one thing that Google Apps Script is missing is modules. NPM has an extremely impressive database of JavaScript modules that don’t automatically integrate with Apps Script. Of course, in Apps Script we have libraries, but the selection is extremely limited and there is no marketplace for those. By the way, who wants to participate in creating one? Let me know in the comments! However, the very first warning on the libraries documentation page notes that libraries make Apps Script slow. Well, Apps Script is already far from being the fastest programming language on Earth, so slowing it down further is not an idea that I’m a fan of!

But what if I told you that there actually is a way to use some NPM modules in Apps Script? You just need to bundle them with Webpack. Not sure what I mean? Keep reading.

Source: The ULTIMATE Guide to NPM Modules in Google Apps Script.