AppsScriptPulse

Reading Qualtrics survey details with Google Apps Script

Use the Qualtrics API to connect with a Survey and get its details.

Qualtrics API Survey details

Qualtrics API Survey details

Most of the complexity with this simply comes in getting the correct syntax for connecting to the Qualtrics API. This Get Survey Qualtrics webpage provides a little bit more technical detail for what exactly is sent/received.

You will need to complete the 3 pieces of information at the top of the Apps Script code for your own setup:

  1. API Token from the Qualtrics website,
  2. ID of the Survey you want the details of,
  3. Your Data Center ID.

Source: The Gift of Script: Get Qualtrics Survey details

Creating a custom workflows using Google Forms and Google Sheets for provisioning virtual machines (VM) in Google Cloud

Is it possible to integrate Google Workspace tools such as Calendar, Sheets, and Forms with Workflows? For example, can you trigger a workflow from a Google Form or a Sheet? Turns out, this is not only possible but also easier than you might think. Let me show you how with a sample use case.

One of the great things about Google Workspace is there is never ‘one’ solution, there is enough flexibility for you to implement one of many workflows. A case in point is this post highlighting a Google Forms/Sheets powered approval system for allocating virtual machines (VM) to your users. As part of this the workflow includes calling the Compute Engine API to create the VM in Google Cloud.

Source: Triggering Workflows from Google Sheets

AppSheet/PayPal integrated payment solutions and tips on handling data in Google Sheets

In this post Iโ€™ll provide an overview of how PayPal was integrated into AppSheet with the help of Google Apps Script. Even if you are not interested in payment integrations this post also hopefully pulls together useful tips, best practices and code patterns for reading/writing data to Google Sheets.

Hopefully this post illustrates not only just a method for integrating a PayPal payment processor into an AppSheet app, but also a method which can generally be used to extend AppSheet functionality with Google Apps Script powered Web Apps.

As a bonus you also get some of my top tips for interacting with data in Google Sheets including efficiently reading/writing data for multiple users without concurrent overwrites.

Source: AppSheet/PayPal integrated payment solutions and tips on handling data in Google Sheets

๐Ÿ“ฎ`MMailApp` for Google Apps Script: GmailApp+MailApp+Gmail API

The library lets you send emails, using MailApp, GmailApp, and Gmail API. The library takes care of parameters, so you do not need to care about which method to use. I had trouble with my project when I wanted to automatically send emails and suddenly for me weโ€™ve reached the daily limit. This is why Iโ€™ve decided to make some research and be sure fewer bad things happen in the future.

If you are looking for a deep dive into Google Apps Script powered email sending options you are at the door of enlightenment. In this post from Max Makrov you not only to get an explanation of the options and benefits for sending email with MailApp , GmailApp and the Gmail API, but also a handy library, MmailApp . which makes it easy to switch between all three.

Source: ๐Ÿ“ฎ`MMailApp` for Google Apps Script: GmailApp+MailApp+Gmail API

Using OCR to convert large images to Google Docs with Google Apps Script (avoiding Request Too Large error)

When the image size, the image file size, the resolution of the image, and so on are large, an error like Request Too Large occurs. In this sample script, such the image can be converted to Google Document by reducing them.

Here is a workaround for converting large images to Google Docs from Kanshi Tanaike. The post includes a code snippet and link to related Stack Overflow Q&A. As noted in the SO discussion the solution might be a compromise for some developers as it reduces the image resolution being passed into Google Drive and if you need to keep the original quality you might have to look at other paid services.

Source: Converting Large images to Google Document by OCR using Google Apps Script

Rose Photo Manager – Bulk copy Google Photos to Google Drive

Rose Photo Manager - Bulk copy Google Photos to Google Drive

If you, like me, miss the automatic sync between Google Drive and Google Photos we lost in July 2019 (as Google thought weโ€™d find it too complicated!) this is the GSheet for you. Rose Photo Manager copies the photos and videos from Google Photos and stores them onto your GDrive

This blog post describes how to set the supplied script to regularly copy your Google Photos from Google Drive (just like how it used to be done!). It builds on this post, but adds a load more features, one being keeping the photos in a GDrive folder with the same name as GPhoto albums that they belong to.

Source: Rose Photo Manager โ€“ Bulk Copy Google Photos to Google Drive

Duplicate Filter Views in selected Google Sheet tabs with Google Apps Script

While there is not way to directly duplicate Filter Views into other sheet tabs in Google Sheets we can do this with a little bit of Google Apps Script magic.

In this tutorial, we will walk through how to duplicate all filter views from a source Google Sheets tab and duplicate them into selected sheets tab.

Weโ€™ll start off with an example sheet to better understand what we are going to achieve and then share the code along with a quick-use guide for those of you who want to to just get in and use the script in your own project.

This tutorial from Scott Donald is a great opportunity to learn more about manipulating Filtered Views in Google Sheets and as a bonus you may pick up some tips on how to use the Sheets Advanced Service.

Source: Duplicate Filter Views in Selected Google Sheet Tabs with Google Apps Script – Yagisanatode

Sending emails with Amazon AWS Simple Email Service (SES) with Google Apps Script

This week was the first time Iโ€™ve tried AWS. I wanted to test AWS SES to write some emails with the help of Amazon. The learning curve was painful for me because I was totally lost in the documentation. Hereโ€™s what I came through

Max Makhrov is on a role with Google Apps Script and Google Sheets development. As well as this latest Medium post looking at Amazon AWS Simple Email Service (or as Max discovered – ‘Not so Simple Email Service’) if not already well worth following Max on Twitter (@max__makhrov) for lots of very useful Google Sheets/Script tips.

If you are hitting your quotas on Google Workspace daily email sends using AWS SES could be one solution. If you want to avoid the bold banners in recipients inboxes here’s some additional required reading on How to Set Up AWS SES and Avoid Spam Folders is essential reading. This covers setting up DNS records for SPF, DKIM, DMARC when using AWS SES. As these changes are required at a DNS level it’s not possible to set this up on regular @gmail.com accounts.

Source: Send Emails with Amazon AWS SES from Google Scripts

๐Ÿ“… A free and open source Google Apps Script web app to make it easy for others to schedule with you

๐Ÿ“… A free and open source web app to make it easy for others to schedule with you –

Features

  • ๐ŸŒ Create a unique link that others can use to book an appointment on your Google Calendar
  • ๐Ÿ“‘ Offer mulitple types of meetings, each with their own configurable timeframe and event settings
  • ๐Ÿ“’ Place events on any calendar you have edit access to
  • ๐Ÿ“† Confirm availability against multiple calendars
  • ๐Ÿค– Intelligent suggestion of available free times on both your and (if accessible) the scheduling party’s calendar
  • ๐Ÿ”— URL parameters to bring the user to a specific meeting type and prefill their email1
  • โšก Send a webhook push to integrate with IFTTT, Zapier, and more when an event is scheduled
  • ๐ŸŒˆ Configurable accent color
  • ๐ŸŒ™ Automatic light and dark mode
  • ๐Ÿ’ณ No premium tier. 100% free.

This Apps Script solution comes thanks to a tweet from Sourabh Choraria (@choraria) highlighting a open source project from Leo Herzog which lets to deploy a highly customisable Google Calendar appointment scheduling app. The solution uses a nice JavaScript library for handling dates/times which you might find useful to include in your own projects called Luxon.

There are some interesting approaches used in this project like checking if there is a newer version of the source code on GitHub. The Luxon library is also fetched/cached and inserted using eval() – the Mozilla MDN web docs have some notes on alternatives to eval() and in the case of Luxon as shown in this Apps Script example you can drop the library into a script file and use it in your code.

Source: GitHub – leoherzog/ScheduleQuest: ๐Ÿ“… A free and open source web app to make it easy for others to schedule with you

Using Google Sheets via BigQuery from Google Apps Script with service accounts

Iโ€™m using the JSON API for BigQuery rather than the Apps Script advanced service, since I have centralized all my BigQuery datasets in one project, and want to use a Service Account for authorization

Google Apps Script includes an Advanced service to interact with BigQuery. A limitation with both the Workspace services and Advanced Services don’t play nice if you need to use a service account. This post from Bruce Mcpherson provides details of how you can setup your Google Apps Script project to use a service account with BigQuery.

Source: Using Google sheets via Bigquery from Apps Script – Desktop Liberation