AppsScriptPulse

Solutions, AppSheet, Sheets, Web App, V8, Intermediate

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

Tags:
| | 0 Comments |

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

Library, Solutions, Gmail, V8, Intermediate

📮`MMailApp` for Google Apps Script: GmailApp+MailApp+Gmail API

Tags:
| | 0 Comments |

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

Solutions, Docs, V8, Intermediate

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

Tags:
| | 0 Comments |

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

Solutions, Drive, Intermediate

Rose Photo Manager – Bulk copy Google Photos to Google Drive

Tags:
| | 0 Comments |

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

Solutions, Sheets, V8, Intermediate

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

Tags:
| | 0 Comments |

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

Solutions, Gmail, Intermediate

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

Tags:
| | 0 Comments |

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

Solutions, Calendar, Web App, V8, Intermediate

📅 A free and open source Google Apps Script web app to make it easy for others to schedule with you

Tags:
| | 1 Comment |

📅 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

Solutions, BigQuery, Sheets, Intermediate

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

Tags:
| | 0 Comments |

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

Add-on, Gotchas, Intermediate

Alice Keeler’s top tips for getting your Google Workspace Add-on published | Google Cloud Blog

Tags:
| | 0 Comments |

If this is your first attempt at submitting an add-on for the Google Marketplace, it—like all new experiences—can take longer than expected as you learn and get comfortable with all of the requirements. You should expect pushback from both the OAuth team and the Marketplace team, as they are on the frontline of ensuring that end users have a positive experience installing Add-ons. Taking the time to slowly go through and make sure you have each of the elements along with a willingness to update and improve your application will surely result in the successful publication of your Add-on published in the Google Marketplace.

Alice Keeler knows a thing or two about publishing Google Workspace Add-ons to the Marketplace with over 20 entries. In this post on the Google Cloud Blog Alice shares some of her top tips for surviving the publication process. This includes website essentials, tips on artwork as well as creating your verification video. Follow the source link for these tips and more.

Source: Google Cloud Workspace Add-On Customization | Google Cloud Blog

Library, Solutions, Sheets, Intermediate

Google Apps Script library if you need to get a Google Sheet as a PDF

Tags:
| | 0 Comments |

Library for converting Google Sheets Into PDF

Features:

  • All PDF settings including colontitles = custom headers and footers.
  • Input parameters is a single plain object.

The library uses the printing features of Google Spreadsheets to provide a complete representation of a document in a different format. It contains a huge amount of features There are settings such as page size, headers and footers, colontitles, gridlines, notes and more.

Source: Max-Makhrov/sheets2pdf_gs: Library for converting Google Sheets Into PDF