AppsScriptPulse

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

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

Get the redirect location of a URL using Google Apps Script

Have a list of shortened urls and need their final destination? Use this custom function in google sheets to extract them all at once.

Handy little snippet that can make testing redirects on URLs a lot easier, which can be useful for expanding short links from social media sites like Twitter.

Source: get the redirect location of a url using google apps script

String validation for Google Apps Script projects

String validation for Google Apps Script projects

Sourabh Choraria has packaged some of the validator.js methods into a Google Apps Script library. Ported validators currently include isUrl and isEmail , date validators and more. Sourabh has also posted the backstory behind this library.

Source: GitHub – validatorgs/validator.gs: String validation for Google Apps Script projects.

Using UrlFetchApp.fetch vs. fetchAll in Google Apps Script

Make multiple, asynchronous API calls simultaneously using UrlFetchApp.fetchAll in Apps Script. mostly when running through a list of resources against which an API call needs to be triggered, we end up using the UrlFetchApp ‘s fetch service; however, in case if the list is well-defined and needs to be consumed as a whole, you could make use of the fetchall service instead.

In this post sourabh choraria highlights the benefits of UrlFetchApp.fetchAll() when making asynchronous API calls in Google Apps Script.

Source: using UrlFetchApp.fetch vs. fetchAll in google apps script

Visually display status when looping through Google Sheets data with Google Apps Script

show a processing status in a cell when looping through rows in google sheets using google apps script.

When working with small batches of data it can be useful to show the user that the data is being processed. Here is an example script from Sourabh Choraria which use SpreadsheetApp.flush() to force write updates to a Google Sheet. As this method relies on .setValue() instead of .setValues() it’s worth keeping in mind for bigger data sets that using HTMLService and a custom dialog or Speadsheet.toast()

If the .setValue() / .flush() Al from coda has provided this gist

Source: visually display status when looping through google sheets data

Format specific word inside a Google Sheet cell using Google Apps Script

Format (bold) a single word or a group of words automatically in sheets, using google apps script.

This example from Sourabh Choraria is a nice example of how you can apply text formatting to parts of Google Sheet cell values. Reading Sourabh post got me thinking if having an Add-on or built in feature that used basic markdown for cell formatting would be useful? e.g. typing `I just love **bold text**` would result in the cell being displayed as `I just love bold text`.

Source: format specific word inside a google sheet cell using apps script

Workspace Devs Directory – a new community initiative to help Google Workspace and Apps Script developers connect

Directory | Workspace Devs

A directory of Google Workspace and Apps Script Developers.

This space is to help bridge that gap within the Google Workspace (previously, G Suite) Developers community and mostly geared towards users who work within the Apps Script ecosystem.

Sourabh Choraria has created a new initiative to help Google Workspace and Apps Script developers connect. Anyone can submit their profile to be included in the directory.

Source: Directory | Workspace Devs

Sending data from a webcaptioner.com channel webhook to Google Sheets with Google Apps Script

push data from web captioner to google sheets via webhooks built using apps script.

the problem – playing with webcaptioner.com which lets you specify a webhook url. i was experimenting with adding captions to a google doc in realtime but apps script couldn’t keep up dropping ~20% of the captions sent via the hook

Sourabh Choraria kindly helped me solve an issue I was having passing data from webcaptioner.com to Google Sheets. Click through to the source link for the full explanation from Sourabh, but in essence the problem was I was losing data because I wasn’t using the LockService .

In the meantime webcaptioner.com have also updated there channel webhooks so you can set a data chuck size so if you are looking to capture a transcript in Google Docs you can use the following published as a container bound web app:

// @OnlyCurrentDoc
function doPost(e) {
const params = JSON.parse(e.postData.contents);
DocumentApp.getActiveDocument()
.getBody()
.appendParagraph(Utilities.formatDate(new Date(), "GMT", "HH:mm:ss")+': ' + params.transcript);
}

Source: web captioner, webhooks and google apps script

Workbook Statistics – a Google Sheets Add-on built using Google Apps Script (lessons in Add-on publication)

Get stats for your current active sheet & the entire spreadsheet in one place using workbook statistics – a g suite editor add-on built using google apps script.

A handy Google Sheets add-on from Sourabh Choraria that can give you an Excel like summary of the Google Sheet you are working on that can display information like the number of: filled cells, formulas, charts and more. This post is also worth reading if you are interested in tips for the Add-on publication processes, highlighting some of the pitfalls you can avoid. The code for the Add-on is also open source making it possible to reuse in your own Apps Script projects.

Source: workbook statistics – a google sheets add-on built using apps script

Reliable, Secure & Scalable use of Google Apps Script | Google Developers Experts

Most conversations around solutions or automations created using Apps Script eventually get to a point where being able to address concerns around its reliability, security and scalability becomes a challenge — more so if compared to having similar applications being built on other platforms (say: AWS, GCP, Firebase etc.).

In this post, we’ll see how to navigate our way through said constructs by addressing parts of these problems and also explore ways in which we can bake some solutions right from the get-go!

In this post Sourabh Choraria provides some very useful answers for common questions organisations have when using Google Apps Script as part of their infrastructure. Points covered in this post include: managing concurrent executions; being able to securely store, access & manage API keys, tokens; and considerations around quotas.

Source: Reliable, Secure & Scalable use of Apps Script | Google Developers Experts

Subscribe to AppsScriptPulse...