AppsScriptPulse

Solutions, Sheets, V8, Intermediate

How to Request Stripe Payments with Google Sheets – Digital Inspiration

Tags:
| | 1 Comment |

Image credit: Amit Agarwal

How to use Google Sheets to generate Stripe payment links and request payments in any currency from your customers anywhere in the world!

Amit Agarwal continues an excellent series of payment themed posts with this latest contribution on integrating the new Stripe Payment Links API with Google Sheets. As noted by Amit this can be combined to make quick payment workflows. Click through for more details and the source code.

Source: How to Request Stripe Payments with Google Sheets – Digital Inspiration

Solutions, V8, Advanced

Generate code verifier and challenge for OAuth2 with PKCE [Using the Twitter API v2 as user with OAuth2 in Google Apps Script] 

Tags:
| | 0 Comments |

here’s how you can generate a cryptographically random code verifier, hash it using sha256 & derive it’s base64 encoded challenge in google apps script.

Continuing the crypto theme Sourabh Choraria has been geeking out with Proof Key for Code Exchange (PKCE) OAuth 2.0 authentication flows which be used in the new Twitter API when making requests on behalf of users.

If you are unfamiliar with PKCE, you can find out more in Okta’s description on how to use PKCE to Make Your Apps More Secure. You can follow Sourabh Choraria post to find out more about the Apps Script implementations of this and the good news for Twitter/Apps Script users is the sample is now committed to the Google Workspace OAuth2 Apps Script library samples as Twitter.gs

Source: generate code verifier & challenge for OAuth2 with PKCE

Gotchas, Level, Solutions

How to use Google Apps Script to process large amounts of data

Tags:
| | 0 Comments |

Senbei is a traditional Japanese cracker made of rice. It is a very tasty cracker that goes well with Japanese tea, but there are also huge Senbeis as big as your face. What do you do when you eat such a big cracker? Usually, you would crush them into small pieces before eating them. The same can be said for processing huge data with Google Apps Script.

Some tips and tricks for handling script runtime and triggers total runtime quotas in Google Apps Script. Make sure you click through to another post from the author for code on An easy way to deal with Google Apps Script’s 6-minute limit.

Source: How to use Google Apps Script to process large amounts of data

Solutions, Sheets, V8, Intermediate

Importing CSV data by keeping number formats of cells on Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for importing a CSV data by keeping the number formats of cells on Google Spreadsheet using Google Apps Script.

When a CSV data is manually put using the default UI using the browser, it seems that the number formats of all cells cannot be kept. In order to import the CSV data to the cells with keeping the number formats, it is required to use a script as a workaround. In this workaround, Google Apps Script is used.

When the following sample script of Google Apps Script is run, a dialog is opened. And, when the CSV file is selected from your local PC on the dialog, the CSV file is loaded, and then, the CSV data is put to the active cell with keeping the number formats of cells.

The quoted post description explains everything you need about this solution and worth clicking through to see how the code works.

Source: Importing CSV Data by Keeping Number Formats of Cells on Google Spreadsheet using Google Apps Script

Solutions, Sheets, Beginner

Archive Sheet data using tickboxes and Google Apps Script

Tags:
| |

Automatically move a row of data from one Google Sheet tab to another when a tickbox is selected.

Archive a row of data by ticking a box

Archive a row of data by ticking a box

The following Google Apps Script is designed to move a row of data from one Google Sheet tab to another when a tickbox has been selected in a given column. The tool was developed to help colleagues with automatically archiving data that had been actioned.

The tool uses an ‘onEdit(e) simple trigger’ to constantly monitor the Google Sheet for any changes in values. Is it specifically targeted at a tickbox-column only so that it does not archive rows of data on every edit of the Google Sheet.

Source: The Gift of Script: Archive Sheet data using tickboxes

Solutions, Sheets, V8, Advanced

Merging Google Sheets from multiple sources and encrypting selected columns – Desktop Liberation

Tags:
| | 0 Comments |

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.

Source: Merging sheets from multiple sources and encrypting selected columns – Desktop Liberation

Solutions, Intermediate

Google Apps Script Release notes as an RSS Feed (scraping web pages with cheerio)

Tags:
| | 0 Comments |

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.

Source: GitHub – tanabee/google-apps-script-release-notes-feed: Apps Script Release notes RSS Feed

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