AppsScriptPulse

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

SuperFetch Plugins: Apps Script streaming with Tank and Drive (Copying very large files to/from Google Drive with Apps Script)

Tank and Drv are SuperFetch plugins to emulate streaming and use the Drive REST API with Apps Script … This article will cover how to copy very large files using Tank to stream and Drv to upload and download partial content. The Apps Script Drive services have a limit on the size of files you can write, and very large memory usage can potentially cause Apps Script to fall over mysteriously

Clever stuff as always from Bruce Mcpherson, this time looking at how you can handle large files with Google Apps Script. We’ve featured some of Bruce’s other SuperFetch posts on Pulse, but developers can benefit from exploring the entire series so far on Bruce’s website. As well as SuperFetch plugins for Firebase and Twitter I’m personally interested in the evolution of the Google Drive client (Drv).

Source: SuperFetch Plugins: Apps Script streaming with Tank and Drive – Desktop Liberation

Google Sheets gets named functions, XLOOKUP and more

In August 2022, Google announced named functions and 9 other new functions in Google Sheets. Named Functions let you save and name your own custom formulas, built with regular Sheets functions, and then re-use them in other Google Sheet files. It’s a HUGE step toward making formulas reusable. Learn about them all here!

Ben Collins is incredibly talented at communicating technical information in a non-technical way. This post is a great example where Ben covers the latest Google announcement on 10 new Google Sheets functions. The two standout ones are Named Functions which lets you make your own reusable function of functions, and something I’m sure many MS Excel users would appreciate, XLOOKUP .

Source: New Functions In Google Sheets For 2022

Starter guide for creating a Google Drive Card Service Add-on with Google Apps Script

Let’s extend Google Drive with Apps Script to create a simple add-on, use CardService for the UI, where we’ll select a few spreadsheets and pass them to the next card with navigation.

For those unfamiliar add-ons for Google Workspace come in a couple of flavours. The Google Editor Add-ons have been around the longest created with Google Apps Script and it’s HTML Service. Google Workspace Add-ons can also be created for the Google Editors (Docs, Sheets, Slides, Forms) as well as Gmail, Calendar and Drive using the Card Service with either Google Apps Script or another runtime environment. This post from Nibesh Khadka provides a great overview of how to create a Google Drive Add-on using Google Apps Script and the Card Service. It covers the basic setup with lots of references to relevant parts of the official developer documentation.

Source: Managing Google Drive with Google Apps Script

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

Autofill Google Sheet Formula with Google Apps Script

Use Google Apps Script to Autofill a formula down rows in a Google Sheet.

Autofill Google Sheet Formula

Autofill Google Sheet Formula

The following Google Apps Script is designed to insert a formula into the first row of data in a Google Sheet, then use Autofill to add the formula to the subsequent rows below. In this example I am just using a simple Sum formula to add up age and shoesize for demo purposes. The need to do this came about when combining multiple Google Sheet files however, that needed calculations adding to the final composed version.

Source: The Gift of Script: Autofill Google Sheet Formula

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

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

Clearing cells in multiple Google Sheets using Google Apps Script

This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script.

Handy little code pattern for clearing ranges across multiple tabs in Google Sheets. Snippets are provided for both SpreadsheetApp and the advanced Sheets service.

Source: Clearing Discrete Cell Values on Multiple Sheets using Google Apps Script

If you use clasp with Google Apps Script, you need this environment switching utility right now!

When working in a team and/or with a client, you want to have multiple environments. At minimum, you probably want a dev environment (or multiple ones) in which you are working, and a test environment in which the client or your team can run acceptance tests before production. Of course, they must both be separate from the production environment. To push your code to the correct environment, you need to either update the .clasp.json file manually or keep multiple copies of your script with different .clasp.json files. Fortunately, things have just become significantly easier, as I recently built an app for this purpose called clasp-env , which is available on NPM. See the source link for details.

Source: “If You Use Clasp With Google Apps Script, You Need This Utility Right Now”

Using Google Apps Script to convert a Google Sheets selected range to an image

Convert selected range into PNG image and save it to Drive

In this post from Max Makhrov you can discover how you can convert a selected Google Sheets range to an image. With options to embed charts and tables in Google Docs and Slides you might be asking why you would want to do this. Max highlights a couple of great reasons:

There are some limitations with this approach which are outlined in the post and if you have any solutions there is a link to contribute to the GitHub repo.

Source: Google Sheets Script. Range 2 Image

How to track link clicks in emails and more with Google Apps Script and Google Sheets

Learn how to track link clicks using Google Sheets and Apps Script to create a simple, lightweight tracking system

You live and learn! This is a regular occurrence if you are a subscriber to the work of Ben Collins (benlcollins.com). No exception with this recent post highlighting the ping attribute which can be used in <a> HTML links. This attribute has been around for a long time, but I’m sure many people like have never come across it. In the source post from Ben you can learn how to setup an Apps Script web app which will let you record link clicks in a Google Sheet.

A note of caution is whilst ping is valid HTML not all browsers choose to use it or enable it by default, something Mozilla have decided to do in Firefox. You can head over to Mozilla mdn documentation for the browser compatibility table and here is also an interesting post on how Google tracks with the ping-attribute.

Source: How To Track Link Clicks With Apps Script And Google Sheets

Introducing ‘Twitter for Google Chat’ – a Chat app built using Apps Script

Preview tweets and user data when sharing links, along with the ability to follow a specific user or like a particular tweet.

If you haven’t looked at Google Chat apps or not looked at Chat apps for a while this is a great post from Sourabh Choraria outlining the development process using Google Apps Script. As part of this Sourabh includes an overview and source code for a Twitter powered Chat app recently published and available to try out in your Google Workspace domain. The app showcases slash commands as well as a recent new feature of preview links.

Source: Introducing ‘Twitter for Google Chat’ – a Chat app built using Apps Script