This is a sample script for converting the values of Google Spreadsheet to an object using Google Apps Script.
I have a minor obsession with methods for converting Google Sheets data to an object array. Over the years I’ve documented various approaches I’ve developed on my personal site, like this one, but this solution from Kanshi Tanaike has to be my new favourite:
I’ve long been interested in using one of the big office productivity platforms to build an embedded add-on. For a solo developer it’s a no-brainer: the platform takes care of the requirements above, and when the platform has a marketplace, there’s even a ready to go distribution channel to take it to market! The only real reason I’ve resisted it so far is fear of the slog of platform approval processes and required collateral. In this story I’ll explain how I felt the fear and did it anyway 😀
Google OAuth verification and Workspace Add-on app review can be quite daunting the first time you go through the process. In this post from Gareth Cronin he shares his own personal journey getting his JIT Time Google Calendar Add-on verified and approved. There are some great tips and resources shared in the post including how to show the OAuth client ID in the add-on demo video submitted as part of the verification processes.
Amit Agarwal has recently been busy continuing to publish lots of incredibly useful Google Apps Script tips and snippets on his ‘Digital Inspiration’ blog. Three recent posts might be of particular interest to Google Workspace administrators:
[This Google Apps] Scripts gets the name and email address of users in the organization and saves the list inside a Google Spreadsheet. This script can only be executed by the domain administrator.
Learn how the Google Workspace admin can change the Google account passwords of multiple users in their organization automatically with Google Apps Script.
This article covers how to pull scripts from multiple projects and import them into another project. You can even use this to make container bound script standalone, or visa versa. … This article will cover the library that does all that, along with various other usage examples – for example, pulling in code snippets from or libraries, merging manifests, or testing add-ons.
Some more magic from Bruce Mcpherson this time creating and documenting a library that can be used to copy/replace scripts. Bruce’s illustrates this with an example of pushing a standalone script to a container bound Google Sheet project and I’m sure you can find many other ways this could be useful to maintain script projects.
Generate dynamic Open Graph images for your website with Google Sheets without requiring Puppeteer. All pages on your website can have their own unique Open Graph images created from a Google Slides template.
As explained by Amit Agarwal Open Graph images are included as a feature image on social media sites when the link is shared. You can use static images but some sites like Github dynamically create an image that includes additional information.
For example, if you were to share the link to the Google Workspace Solutions Github repo on Twitter an image is automictically displayed containing information like the number of contributors, issues, stars and forks:
To generate these images often a headless browser solution like Puppeteer is used. In this example from Amit he shows how a Google Slides template and a little Google Apps Script can be used to batch create similar open graph images.
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.
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.
