AppsScriptPulse

Report: Implementing a pseudo 2FA for Web Apps using Google Apps Script

In Google Apps Script, there is the Web Apps. When Web Apps is used, the users can execute Google Apps Script using HTML and Javascript. This can be applied to various applications. When the Web Apps is deployed with “Anyone”, anyone can access the Web Apps. And, there is the case that Web Apps deployed with “Anyone” is required to be used. Under this condition, when 2 Factor Authentication (2FA) can be implemented, it is considered that the security can be higher and it leads to giving various directions for the applications using Web Apps. In this report, I would like to introduce the method for implementing the pseud 2FA for Web Apps deployed with “Anyone” using Google Apps Script.

There can be scenarios where you’d like to publish an Apps Script web app with ‘anyone can access’, but still provide a level of security. Here’s a nice example from Kanshi Tanaike where they use MailApp to email the user a a time limited random passcode.

Magic links and passwordless login are used by a number of services, including Slack, and rather than including a password, as demonstrated in this example, it wouldn’t take much to turn this solution into a passwordless app. There are risks associated with email based authentication and if an attacker already has access to your email so other solutions are worth considering depending on the sensitivity of your web app.

Source: Report: Implementing Pseudo 2FA for Web Apps utanaikech.github.iosing Google Apps Script

Get hidden or visible Google Sheet tabs with Google Apps Script

Learn how to list hidden Google Sheets tabs with Google Apps Script – CODE and VIDEO tutorial with script explainers.

This post from Scott Donald is an opportunity to learn about how you can work with hidden tabs in Google Sheets with Apps Script. For beginners this post is also an opportunity to learn about the JavaScript reduce method as a way to iterate through data. As well as the code and supporting video the author Scott Donald has some related projects where you can see this solution in action.

Source: Get Hidden or Visible Google Sheet Tabs with Google Apps Script – Yagisanatode

[Workaround] An alternate to displaying more than 100 widgets/sections in a Google Workspace Add-on

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.

Source: [Workaround] An alternate to displaying more than 100 widgets/sections.

r/GoogleAppsScript – AutoScript – create Google Apps Script code with help of AI #nocode

I’ve seen a couple of proof of concept solutions for AI generated Google Apps Script code, but this one is the most well developed I’ve seen to date. The AutoScript Add-on isn’t fully verified so if you are trying this out I would recommend using disposable credentials (as a Google Workspace admin I created/used a Cloud Identity account). I’ve only done limited testing but was able to get the add-on to generate a functional Google Apps Script code snippet. Simultaneously both amazing and scary to think a chunk of what I do for a living could be replaced by one of our robot friends.

Develop a Google Chat App Currency Converter with Google Apps Script – Yagisanatode

Have you ever wanted to convert currencies instantly while in Google Chat with colleagues and clients? In this tutorial, we are going to build a Currency Converter Google Chat App with Google Apps Script to do just that.

This tutorial is a multi-media series containing step-by-step video instructions for each stage of the process along with code snippets for each stage, important links and some further details on the more unusual parts of the code.

This is a great video series wrapped in a blog post, which highlights a Google Chat app development process. The particular project presented by Scott Donald is a currency converter but there are many repeatable aspects that would work in a wide range of Google Chat applications. The project is coded in Google Apps Script and the post includes plenty of snippets to help you understand various aspects of Google Chat app development including slash commands and information cards.

Source: Develop a Google Chat App Currency Converter with Google Apps Script – Yagisanatode

Bulk convert Google Sheets to PDFs using Google Apps Script

Bulk convert Google Sheets within a given Google Drive folder into PDFs and optionally delete the original file.

Bulk convert Google Sheets to PDFs using Apps Script

Bulk convert Google Sheets to PDFs using Apps Script

Features

  • Maximum runtime – in order to prevent the tool from reaching the limits imposed by Google you can adjust the number of minutes the tool can run for. Change this in the ‘GlobalVariables.gs’ file in the Script Editor.
  • Continue from where it left off – if you have a lot of Google Sheets to convert and the above runtime is reached the tool will save its progress and prompt you to run it again, avoiding any file duplication.
  • HTML popup – as well as the ‘Log’ sheet the tool displays a direct popup to the user if it encounters a problem.
  • PDF counter – after successfully running the tool will include the number of PDFs created as part of the success popup to the user.

Source: The Gift of Script: Bulk convert Google Sheets to PDFs

Workaround: Checking Existence of File ID in Google Drive without Access token and API key using Google Apps Script

This is a workaround for checking the existence of file ID in Google Drive without both the access token and API key.

When you want to check whether the file of the file ID is existing in Google Drive, generally, you might use Drive API and Drive service (DriveApp) of Google Apps Script. In this case, the scope of Drive API is required to be used. By this, the access token and the API key (in the case of publicly shared files) are required to be used. But, there might be a case that the available scopes are limited. In this post, I would like to introduce a workaround for checking the existence of file ID in Google Drive without both the access token and API key.

This workaround could have been a nice addition to a recent project I was working on to audit a bunch of Google Drive file IDs. As noted in this post a big benefit of the approach is there is no need to include Google Drive authentication scopes to your project. See the source post for the code and explanation.

Source: Workaround: Checking Existence of File ID in Google Drive without Access token and API key

How to monetize your Google Workspace add-on?

You’ve built a great add-on for the Google Workspace ecosystem (Google Docs, Google Sheets, Gmail & co.). It’s getting a lot of traction and you feel like you can monetize it. This is the guide I wish I had found a few years ago when I was researching how to monetize our own add-on for Google Sheets.

Here is a very concise and incredibly useful guide written by Corentin Brossault, co-founder and CTO at Mailmeteor, on the practical steps you can take if you are interested in monetizing your Google Apps Script solution. The post includes some code snippets and guidance on protecting personal information of your users, storing licence data in Firebase and integrating with Stripe as a payment processor.

Source: How to monetize your Google Workspace add-on?

Introducing AppSheet databases: Build data driven apps for Google Workspace

Over the past year, we’ve added more functionality to AppSheet, extending how it can maximize the power of Google Workspace through integrations with products such as Gmail, Google Drive and Apps Script. To improve the experience for app creators and users, we’re excited to introduce in public preview AppSheet databases, a built-in database for citizen developers to easily and securely manage their data.

During public preview, access to AppSheet databases will be enabled by default for everyone but it will not affect existing apps. Use of this feature in public preview will be included at no additional cost in your AppSheet subscription plan, but limited to 10K rows per table, 20 tables per database and 20 databases per user. Please note that these limits may change when the feature is generally available.

For people who tuned in to TU3.5: New workflow solutions with Tables and Google Apps Script back in 2020 AppSheet databases will look very familiar. Since that episode the Area 120 team behind the Tables beta moved into Google Cloud and specifically AppSheet.

The big question for me is whether AppSheet databases can find a sweet spot for citizen developers looking for something with more performance than a Google Sheet and easier to setup than a Google Cloud project. In public preview AppSheet databases are limited to 10,000, which interestingly is the same limit as the old paid version of Tables.

Ultimately this may however be less able size and performance but a data solution that the AppSheet team has full control, particularly removing a reliance on features developed by the Google Sheets team.

Source: Introducing AppSheet databases: Build data driven apps for Google Workspace

Create automated backups of Google Sheets with Google Apps Script

The problem comes when you have to load that info in your app. … The more the data, the more time to load. You could use Apps Script to schedule backups of your data.

A nice introduce into Google Apps Script with an opportunity to learn how to archive data from one Google Sheet to another. This solution comes from Mozart García who is more often seen on YouTube producing short Google Apps Script how-to in Italian (Bravo! Mozart on recently passing 4K subscribers)

Source: Create automated backups with Apps Script

Using Google Apps Script to get Google Workspace user profile pictures without using the Admin APIs

Image credit: Google

Chances are that you may need to display user’s picture profiles in your applications. However, this API is part of the Directory API, which is part of Google Workspace Admin area. And chances are that your Organization has simply not giving you the rights to use Admin APIs

Sometimes within your Google Workspace domain you’d like to include account profile images. A number of Google Workspace APIs, like the Drive API, will include profile pictures in the response object. When this isn’t the case this post by Turhan Oz is a nice reminder that the dedicated People API can be used to get profile images. Click through to the source post to read more and sample code.

Source: AppScript — get user profile picture without using Admin APIs

How you can use Google Docs to write and execute Google Apps Script code

Image credit: Amit Agarwal (labnol.org)

There is a way to use Google Docs as a programming IDE and run JavaScript code inside the editor … It is no replacement for a dedicated IDE like Visual Studio code but Google Docs can be used as a JavaScript playground to quickly run code snippets.

A familiar story I hear is people with no formal coding experience starting their developer journey with Google Apps Script. I find being able to run small snippets of code are a great way to learn and a number of sites like W3 Schools and also increasingly Stack Overflow include the ability to ‘try it yourself’ with inline code runners.

If you are looking to support users getting started with Google Apps Script Amit Agarwal has this little code snippet which lets you inline/run Google Apps Script code in Google Docs. There are both limitations and considerations with this approach but as a quick way for learners to run and collaborate on small snippets, which can be contextualised with explanatory text/resources, it’s a nice starting point. Check out the source link for more details.

Source: How to Use Google Docs as a Code Runner – Digital Inspiration

Uploading multiple records with the Salesforce API using Google Apps Script

Image credit: Eduardo Ramon Resser

Sometimes, when using Salesforce, we come across the situation of needing to upload several object records at once, such as accounts, contacts, tasks, etc.

There are several ways to resolve this issue. One approach that can be efficient is through Google Sheets, especially if you want to “convert” each row in a table into separate records in Salesforce.

In the example of this article, I will talk about an implementation for bulk exporting of tasks using information contained in a table in Google Sheets using the Salesforce API.

This post on interacting with the Salesforce API highlights the tricky situation of handling different user credentials. The author comes up with a clever solution of using user owned Google Docs as a way to store different identities. Another approach is to use Google Apps Script’s Properties Service. The Properties Service includes an option to store User Properties which are only accessible to the user running the script. The downside of this approach is you then have to create UI for the user to input properties.

Source: Uploading Multiple Records Via Salesforce API Using Google Apps Script

Import CSV data into Google Sheet using Google Apps Script

Loop through CSV files in Google Drive and extract their contents into specific areas in Google Sheet files.

CSV File template to copy data into

CSV File template to copy data into

The following Google Apps Script is designed to loop through a folder of CSV files in Google Drive, extract the data, create a Google Sheet file per CSV, and insert the data into specific rows/columns.

Source: The Gift of Script: Import CSV data into Google Sheet files