An approach to process hundreds of thousands of rows of data (say, on Google Sheets) without running into the script execution timeout error.
TL;DR — the typical challenge we face with tasks that take longer to execute is that of running into the execution timeout error (that’s at 6 minutes — Script runtime — as of 10-Feb-2023) and this article … elaborates on how to bypass script execution timeout with a detailed example.
As noted in this post by Sourabh Choraria the Apps Script runtime limit can be a bit of a challenge. If you’re encounter problems my first tip would be review your script and see what you can optimize. A common problem I often see in Google Sheets is using .getValue()/.setValue() (singular) not .getValues()/.setValues() (plural).
If even with script optimization you are still having issues Sourabh provides a very adaptable ‘design pattern’ for Google Sheets. The pattern is one I can personally relate too and very similar to the approach I used in my own projects. The source post has everything you need to try this out for yourself.
Working around the 100-widget limit in Card Services, used when building Workspace Add-ons.
Like it says on the tin a way to display more than 100 items in a Google Workspace Add-on. The solution also has a nice UX friendly search feature to make it easier to find stuff in the UI. The source post from Sourabh Choraria includes additional information and links to the code on GitHub.
Preview tweets and user data when sharing links, along with the ability to follow a specific user or like a particular tweet.
If you haven’t looked at Google Chat apps or not looked at Chat apps for a while this is a great post from Sourabh Choraria outlining the development process using Google Apps Script. As part of this Sourabh includes an overview and source code for a Twitter powered Chat app recently published and available to try out in your Google Workspace domain. The app showcases slash commands as well as a recent new feature of preview links.
A community connector with overridable config parameters to track recent Twitter trends for a query.
For the most part this connector is pretty similar to the one I’d created for visualising global stats of SendGrid user’s email statistics but with a key difference of making use of overridable config parameters — this is what gives the connector the capability to accept a query from the report directly and return/visualize the data based on a user’s input
This post from Sourabh Choraria is a useful reminder of what is possible with Data Studio Community Connectors, in this case connecting to the Twitter API v2 to visualise the tweet count for a defined search term.
The code for this connector is available from the source post and includes lots of inline comments to help you work out what is going on making it easy to modify if you have other APIs you would like to connect.
Behind the scenes look at what went into creating an Apps Script-native Add-on to generate Webhooks for Google Sheets. ICYMI: You can access the add-on from this link and know more about what it does here
It’s worth checking out Sourabh Choraria’s latest Google Sheets add-on which enables users to quickly setup a Google Sheet to receive data from other services which support the creation of webhooks. As part of this solution the add-on makes extensive use of the Apps Script API, which can be used to programmatically manage Apps Script projects including deployments. This post from Sourabh highlights some of the key endpoints used in the Apps Script API as well as a number of lessons learned about deploying web apps for users.
Convert array values from the doGet & doPost event parameters into 2D array when writing them onto a Google Sheet.
Sourabh Choraria has hit a rich patch of Google Apps Script exploration and following the post we shared recently on Google Sheets cell precedents in this latest post Sourabh looks at restructuring data into a cartesian product. The post contains some nice other ‘Easter Eggs’, in particular, worth checking out Sourabh’s latest Workspace Add-on, Webhooks for Sheets, and a snippet of code on GitHub used in the add-on for handling GET and POST requests.
enumerate a range in google sheets using google apps script and use that to find precedents of cells that contain a formula.
Let’s start with the ubiquity of spreadsheets. I’ve long felt that spreadsheets were everywhere but until a post from Hjalmar Gislason I had never seen it in numbers and it made for interesting reading:
1.2 billion people use Microsoft Office (WindowsCentral, March 2016), odds are most of them have at least access to Microsoft Excel.
Excel is the number one skill mentioned in job ads, mentioned in approximately 1 in 3 job ads! (Indeed.com Job Trends, June 2017).
In 2010, RescueTime found that about 25% of computer users used Excel on a daily basis and that about 2% of all time spent on a computer anywhere was spent using Microsoft Excel.
As useful as spreadsheets are the underlying data structure can cause problems. This is covered in more detail in Patrick Burns’ Spreadsheet Addiction, which amongst other things highlights the ambiguity of value and formula:
Primarily the problem is that some cells have hidden meaning. When you see a number in a cell, you don’t know if that is a pure number or a number that is derived from a formula in the cell. While this distinction is usually immaterial, it can be critical.
The leading example is sorting. When rows are sorted, usually it is desired to sort the numbers as they are. However, it will be the formulas that are sorted. To humans the numbers are the primary thing, and the formulas are just something in the background. To the spreadsheet the formulas are primary, and the numbers are mere results.
This is a long winded way to highlight some work from Sourabh Choraria which is exploring how the relationship between formulas and cells can be extracted in Google Sheets. As part of this Sourabh is looking at how the getFormulasR1C1 method in SpreadsheetApp can be processed to find precedent cells. There are some very funky use of regex to parse formulas which is worth spending some time to digest.
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
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.
Sourabh Choraria has packaged some of the validator.js methods into a Google Apps Script library. Ported validators currently includeisUrl and isEmail, date validators and more. Sourabh has also posted the backstory behind this library.
