Google Apps Scripts is incredibly powerful and enables complex systems to be built on top of Google Apps. It can be a great choice when you need to quickly prototype an idea or design a solution that’s customizable by non-technical users.
In this article, I will walk through a simple example of building a “Slot Booking System” using Google Sheets, Google Calendar, HTML, Tailwind CSS and Google Apps Script.
Nice little Apps Script project shared in this post using Google Calendar and a published web app, making it possibly a nice starter project for something bigger.
This article explains how the filtering criteria of Google Sheets slicers can be reset using a few lines of Apps Script code. Two different approaches are shown. The first one uses the Spreadsheet Service, while the second one draws upon the Google Sheets API using the Advanced Sheets Service. Additionally, some shortcomings and peculiarities of both methods are highlighted.
Kudos to Pablo Felip for taking the time to explore and write-up this post about Google Sheets ‘slicers’. It contains some very useful tips and advice about interacting with them using Google Apps Script.
This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.
A common Google Apps Script use case is taking data from a webform and adding it to a Google Sheet. This can sometimes be quite painful has you have to handle the various input types and also write the data to the correct columns. The HtmlFormApp library makes this very straight forward and to illustrate once the library is added to your Apps Script project you can start appending data in a couple of lines of code:
// These are all options.
const obj = {
formData: formData,
spreadsheetId: "###",
sheetName: "###",
sheetId: "###",
folderId: "###",
headerConversion: {"header value of Spreadsheet": "name of HTML input tag",,,},
ignoreHeader: true,
choiceFormat: true,
delimiterOfMultipleAnswers: "\n",
valueAsRaw: true
};
const res = HtmlFormApp.appendFormData(obj);
console.log(res)
Click through to the source link for more details 👇
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.
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
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.
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.
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.
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.
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.
