AppsScriptPulse

AppSheet/PayPal integrated payment solutions and tips on handling data in Google Sheets

In this post I’ll provide an overview of how PayPal was integrated into AppSheet with the help of Google Apps Script. Even if you are not interested in payment integrations this post also hopefully pulls together useful tips, best practices and code patterns for reading/writing data to Google Sheets.

Hopefully this post illustrates not only just a method for integrating a PayPal payment processor into an AppSheet app, but also a method which can generally be used to extend AppSheet functionality with Google Apps Script powered Web Apps.

As a bonus you also get some of my top tips for interacting with data in Google Sheets including efficiently reading/writing data for multiple users without concurrent overwrites.

Source: AppSheet/PayPal integrated payment solutions and tips on handling data in Google Sheets

Working with the Google Drive API Revisions history: Tips for handling revision merges with Google Apps Script

Having spent quite a bit of time working with the Google Drive API Revisions resource in this post I thought it would be useful to share some of the lessons and solutions I’ve picked up along the way. For this I’ll be sharing code snippets for interacting with the Revisions resource with Google Apps Script, but the solutions discussed could easily be applied to your programming language of choice.

Source: Working with the Google Drive API Revisions history: Tips for handling revision merges

Automatically running a Google Apps Script function every quarter or another monthly period greater than one month

One of the great things about Google Apps Script is the way you can automate tasks. I’ve previously written about how I automate reporting and other examples like running backup processes. These usually run daily or once a month which is very straight forward to setup in Google Apps Script. If you want to run a script automatically every x number of months such as quarterly it gets a little harder. If you only want you script to run every three months … another option is to manage triggers programmatically which allows you to specify the date a function should be run again.

I’ve recently been revisiting some of my old Google Apps Script posts to do a bit of housekeeping. I thought this was a nice little snippet should you want to schedule a function to run on a time-driven trigger greater than one month. The trick used is to recursively create a time-driven trigger when the function is called. The solution comes with some caveats :)

Source: Running a Google Apps Script function every quarter or x months

Google Apps Script developer notes on using the new Google Forms REST API

Google recently announced the general availability of the new Google Forms API. For developers familiar with Google Apps Script for some time it has been possible to create, access, and modify Google Forms through the Forms Service. In this post I will highlight why Google Apps Script developers might want to use the new Forms API and some resources I’ve found useful for working with the new API in Apps Script projects. As part of this I’d like to share my experience of using the Forms API within a Google Forms Add-on and some factors to consider if you are particularly interested in watching for Google Form edits.

The Google Forms REST API provides an alternative method for managing Google Forms and responses. For Apps Script developers it is likely you’ll want to stick with using the Forms Service, but the new API is useful to know about as it opens up some new ways for managing, editing and watching Google Forms.

Source: Google Apps Script developer notes on using the new Google Forms REST API

Creating a ‘full fat’ RSS feed for Google Gmail labels with Google Apps Script

In this post I want to cover three things. First I want to introduce a little app I’ve developed which allows you to create a RSS feed for any of your Gmail labels (with the option to remove certain links – useful if you don’t want others unsubscribing you from mailing lists). Secondly I explain how it was made and how you can use it yourself. Finally I want to discuss how this could be used in an open course environment, utilising the vast processing power from services like Twitter and reusing their target marketing emails to your benefit with a bit of ‘dark social judo’.

This solution was first published in May 2013 and since then Google Apps Script has evolved deprecating services that originally made this solution possible, in particular, ScriptDB and the original XML service which includes a handy .parseJS() method.

Following a request I’ve recently updated the solution to make it work again. As well as swapping out ScriptDB in favor of using the Properties Service I used the makeRSS method previously highlighted here in Apps Script Pulse.

Source: Creating a ‘full fat’ RSS feed for Google Gmail labels (enabling some dark social judo)

A SyntaxHighlighter Evolved WordPress plugin for Google Apps Script contributors

With my published content I like to include useful code snippets. As I published on a self-hosted WordPress blog I’ve used plugins to help format the code to make it easier to understand and also aesthetically look nice. In this post I share a WordPress plugin I’ve developed for Google Apps Script syntax highlighting.

Source: A SyntaxHighlighter Evolved WordPress plugin for Google Apps Script contributors

Adding an unsubscribe link to the Google Workspace Solution Gallery ‘Mail merge using Gmail and Google Sheets’

Recently I came across a nice post from Ravgeet Dhillon, which looked at how to Add Unsubscribe link in emails using Google Apps Script. The solution uses a Google Sheet and a Google Apps Script web app to add an unsubscribe link to emails. As I noted when shared via AppsScriptPulse this is a useful addition if your mailing needs comply with PECR or similar electronic communications regulation compliance.

Having recently updated my ‘Create a mail merge using Gmail and Google Sheets’ in the Google Workspace Solution Gallery to handle inline images I thought it would be useful to show how this example can be extended to include a variation on Ravgeet’s post.

Source: Adding an unsubscribe link to the Google Workspace Solution Gallery ‘Mail merge using Gmail and Google Sheets’

Everything you ever wanted to know about Gmail draft inline images and Google Apps Script but were afraid to ask

Image order in Gmail draft

A common workflow solution in Google Apps Script is to use a Gmail draft message as a template for sending emails. For the user the benefit is they can draft a message in a familiar environment adding formatting without having to worry about writing HTML. A problem often encountered is the inclusion of inline images. This post looks at solving the issue of missing inline images.

Source: Everything you ever wanted to know about Gmail draft inline images and Google Apps Script but were afraid to ask – MASHe

Totally Unscripted Exclusive: Detailed look at the new Google Apps Script IDE/Script Editor and launch

New Apps Script Editor

In the episode we broke the exclusive news that the new Script Editor will be rolling out to all users starting on 07 December 2020. I can’t thank Keith enough for taking the time out to share this news to the Totally Unscripted audience first and also to Charles for making possible as well as providing an expert tour of the new Apps Script Editor.

More official news and resources will be published next week and we’ve setup a category on AppsScriptPulse to capture these and community contributions.

If you missed the live episode the recording is included in the source post.

Source: Totally Unscripted Exclusive: Detailed look at the new Google Apps Script IDE/Script Editor and launch – MASHe

Working with Pivot Tables in Google Sheets using Google Apps Script

Pivot tables are a quick and powerful way to analyse data. Using Google Apps Script, there is an ability to build and modify pivot tables in Google Sheets. In this post I’ll share some tips and tricks for interacting with pivot tables in Google Sheets.

Source: Working with Pivot Tables in Google Sheets using Google Apps Script

Adding some AutoFill magic to your Google Sheets projects with Google Apps Script

Google Sheets users can already use the magic of AutoFill to expand data automatically detecting a series of numbers, letters or dates. This feature is particularly powerful even when your series includes text or repeating dates. In this post I’ll highlight how you can also use AutoFill in your Google Apps Script projects

This post is designed for both no/low coders and expert developers interested in finding out how you can use AutoFill in your Google Sheets/Apps Script projects.

Source: Adding some AutoFill magic to your Google Sheets Apps Script projects

Dashboards in your inbox – Revisting tips on emailing inline Google Sheet chart images with Google Apps Script

Back in 2015 I shared a post on Tips on emailing inline Google Charts from Sheets using Apps Script, which looked at how you can email charts from Google Sheets. This solution is one I use in my work on a daily basis and remains largely unchanged. More recently I was faced with the problem that I couldn’t include a Combo Chart. In this post I revisit this problem and share a solution for including copies of your Google Sheet charts in your email reports.

Whilst this solution focuses on extract Google Sheets chart images for the purpose of emailing, the technique might be of interest for other situations where you need to use a chart image.

Source: Dashboards in your inbox – Revisting tips on emailing inline Google Sheet chart images with Google Apps Script

First look at the new Google Apps Script Integrated Development Environment (IDE) Script Editor – MASHe

As part of Next OnAir week 2 the focus was on ‘Productivity and Collaboration’ Charles Maxson from the G Suite DevRel team not only provided some highlights from the week, but he also premiered the new Google Apps Script IDE.

This post includes a recording to a live demo of the new Google Apps Script editor. No news on when the new Script Editor will be generally available, but from the demo it looks like Google have focused on making it easier for developers to code and debug scripts.

Source: First look at the new Google Apps Script Integrated Development Environment (IDE) Script Editor – MASHe

A bulk email/mail merge with Gmail and Google Sheets solution evolution using V8 – MASHe

Last year I had a ‘mail merge using Gmail and Google Sheets’ contribution accepted on the G Suite Developers Solution Gallery. Just over 6 months on there has been lots of useful feedback and requests for solutions to some of the limitations of the original published script. In the meantime Google has also made the new V8 runtime for Google Apps Script generally available. Given this it seemed appropriate to revisit the original solution updating it for V8 as well as covering the most commonly asked for changes. In this post I’ll highlight some of the coding changes as well as some easy modifications.

This post picks up some common requests I get for features like advanced send parameters (cc, bcc, sender name/from, replyTo), sending emails with emoji/unicode and scheduling/triggering bulk emails. Some other areas covered in this post might be of general interest to Apps Script developers interested in using formatted Google Sheets cell values for currencies, dates and more as well as detecting/ignoring filtered hidden rows.

Source: A bulk email/mail merge with Gmail and Google Sheets solution evolution using V8 – MASHe