AppsScriptPulse

2-Way sync between sheets without a unique ID

How to setup 2-way sync within the same workbook without any unique ID.

I’m in the habit of adding IDs to sheets where I need to do lookups, but sometimes, I inherit a sheet without IDs in place (or I can’t edit the sheet structure). Spencer outlines a clever way to handle syncing between two sheets without using IDs in this video.

Source: 2-Way Sync: No Unique ID

Pseudo OnEdit Trigger for Google Document using Google Apps Script

In the current stage, there is not OnEdit trigger for Google Document. But I sometimes have the case that I want to use OnEdit trigger. So, as the current workaround, I created this sample script. I think that this workaround can be also used for Google Slides, Google Form and also Google Spreadsheet. In the case of Spreadsheet, what the existing OnEdit trigger and the time-driven trigger cannot do can be achieved.

Source: Pseudo OnEdit Trigger for Google Document using Google Apps Script

How to Automatically Add Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

Learn how to instantly share newly enrolled teachable students to Google Drive files and folders with Google Sheets and Apps Script.

Scott ‘Yagi’ Donald provides a very thorough walk-through of how he has connected a webhook from a third party service to Google Sheets to share specific Google Drive folders and files with named users. This post is well worth a visit as it contains a number of tips and tricks like using Google Forms to follow-up with user email addresses that are not associated with a Google account.

Source: How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

How to Fetch Employee Data from Workday by Building the Great Deku Tree Inside of Google Sheets

They say you only live once, and, with that, you may only once find yourself at a company with a video-game-centric culture. Life at Twitch creates a unique opportunity to bring the magic of gaming to your workday, even when you’re in esoteric corners of the work world such as People Analytics.

Ever wondered how people reporting was handled at Twitch? Max Brawer, Head of People Analytics at Twitch, brings his ‘A’ game outlining how a little Google Apps Script and some Workday API wrangling can be used for some fun reporting. You can read more in the source link and here’s Max’s old post on The Endless Possibilities of Freeing Your Data from Workday and Putting it into Google Sheets: a How-to.

Source: How to Fetch Employee Data from Workday by Building the Great Deku Tree Inside of Google Sheets

Letting users run a Google Apps Script on Google Sheets without authorizing scopes and showing the source code

This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

The post highlights the use of the existing =IMPORTML() Google Sheets function and a publish webapp to run a Google Apps Script without the end user having to authorize the script. A limitation of this approach, and also important warning to keep in mind, is the deployed web app needs to be deployed to run as the script owner and by anyone who has the web app link.

Source: Letting Users Running Google Apps Script on Google Spreadsheet without both Authorizing Scopes and Showing Script

Keep a Journal of Special Moments using Twilio Programmable SMS and Google Sheets

Learn how to build a Google Sheet journal that accepts new entries via text message. My toddler recently turned two and a half and my second daughter was born three weeks ago, so special moments are abundant but the ability to remember them is severely compromised. His suggestion of recording a memory every day in an Excel sheet or leather bound journal resonated with me, but after a few days of trying to find time to open up Excel and write a memory I realized I needed a more lightweight solution. … Twilio makes it easy and cheap to set up a dedicated phone number that can record those text messages to a Google sheet.

Nice example on the Twilio blog on using their service with Google Sheets. There is little setup required but this can all be achieved in 4 lines of code. Unfortunately, sending/receiving SMS messages with Twilio is not free, but it might be a service you keep in mind for Apps Script projects.

Source: Keep a Journal of Special Moments using Twilio Programmable SMS and Google Sheets

Enhanced menus in Google Sheets improves findability of key features (and an important update for Google Workspace Developers)

New Google Sheets menus
New Google Sheets menus

We’re updating the menus in Google Sheets to make it easier to locate the most commonly-used features. Some of your favorite menu items may have moved a little, but all existing functionality is still available. We hope that their new home will be more intuitive and make it easier and faster to navigate the product.

Google Workspace Updates

Google have updated the menus in Google Sheets to help with discoverability. For Google Workspace Developers one of the big changes is the new Extensions menu which replaces ‘Add-ons’ and includes links to Macros, Script Editor and AppSheet. Whilst painful if you need to update support documentation the new structure will hopefully make the location of these tools more intuitive. We covered the Google Sheets menu update in the latest episode of Totally Unscripted:

Source: Enhanced menus in Google Sheets improves findability of key features

Bulk create Google Drive Folders and add Files 2.0

Bulk create Google Drive folders with optional files copied into each one. With full control over naming.

Bulk create Google Drive folders with optional files, from a Google Sheet

Bulk create Google Drive folders with optional files, from a Google Sheet

This post is a variation of this Bulk create Google Drive Folders and add Files tool post.

Key Functionality

  1. 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.
  2. Continue from where it left off – if you have a lot of Google folders and files to handle and the above runtime is reached the tool will complete the current row/folder and prompt you to run it again, without duplicating any folders or files already created.
  3. Popups – as well as the ‘Log’ sheet the tool displays a direct popup to the user if it encounters a problem.
  4. Full naming control – you have the ability to tweak every single folder and file name that is created by the tool. See the Concatenate section below to understand the true power of this.
  5. Clickable links – created by the tool which means you can navigate directly to the new folders and files directly from the Google Sheet.
  6. Add more files – the tool currently has columns for 10 files to be added to each folder, but you can actually append more as long as you follow the same convention of 2 columns per file (1 file name and 1 file ID). Do this before you run the tool as you cannot add more files to a folder once it has been created.
  7. Add more folders – after running the tool you can either choose to clear all of the data within it (use the ‘Reset sheets’ option from the ‘Admin’ menu) and start again or you can simply append further rows/folders and re-run without any duplication (if the ‘Folder Link’ column is not blank/empty then it will skip that row).

Source: The Gift of Script: Bulk create Google Drive Folders and add Files 2.0

Creating a web app with Google Sheets

A wonderful set of video tutorials showing you how to set up a web app and connecting it to a Google Sheet. It starts off with a simple form and covers areas like getting data from a sheet and sending it back and using Materialize CSS for to easily create a good looking app. Plus, it also looks at connecting a Google Calendar to the app and how to create multiple pages in the web app.

Issues reporting with translation [using Google Apps Script and Google Forms/Sheets

In this post Baz Roberts highlights the benefits of Google Apps Script Language Service to translate Google Form responses into another language.

Source: Issues reporting with translation

Create a book tracker using Apps Script and the Google Books API in 10 minutes

I wanted a quick and easy way for my kids to track the books they read, as well as a way for my wife to catalog all the books in the house. I know there are other services out there like Goodreads that can store your book list…but I just wanted to house the data myself, and not have to create accounts for my young kids.

Nice little project that glues together Google Forms and Sheets and the Google Book API with a little but of Google Apps Script. Follow the link for more information and the source code.

Source: Create a book tracker using Apps Script and the Google Books API in 10 minutes

Bulk convert Google Docs to PDFs 2.0

Bulk convert all the Google Docs within a given folder to PDFs and optionally delete the Docs afterward, version 2.0

The following Google Apps Script tool is designed to bulk convert all Google Docs within a given Google Drive folder into PDFs. You can choose the destination folder for the PDFs to be put into and also whether you want the original Docs to be deleted. This tool does work on Shared drives.

It is an improved version of this Google Doc to PDF tool post.

Improvements / Features

  1. 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.
  2. Continue from where it left off – if you have a lot of Google Docs 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.
  3. HTML popup – as well as the ‘Log’ sheet the tool now displays a direct popup to the user if it encounters a problem.
  4. 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 Docs to PDFs 2.0

Create Google Classrooms using Google Apps Script

Creating a Google Classroom and maintaining it can be a bit hectic for so many students in a school. Say you are the moderator of your school and your task is to create 10 Google Classrooms for different teachers. Manually doing this can be a very hectic process, that’s where Google Apps Script comes in.

Using Google Apps Script, you can automatically create Google Classroom with the data in the Google Sheet, and paste the Class code in the Google Sheet.

Source: Create Google Classrooms using Google Apps Script

Multiple Sheets Action. Use Macro Recorder 🔴

Image credit: Max Makhrov

The idea: perform the same action on multiple sheets. The idea is not new, my approach was to use macro each time to see the correct code snippet. It was useful and saved me hours on my current project.

A clever solution from Max Makhrov with some boilerplate script that makes it easy to use a recorded macro across multiple sheets. To make this solution even better I’ve suggested an addition which activates the sheet which makes it easier to reference recorded macro functions. To see in action here is a copy of Max’s Google Sheet with updated code.

Source: Multiple Sheets Action. Use Macro Recorder 🔴

[Testing] Concurrent writing to Google Sheets using Google Form and HTML form

When the users try to write to Spreadsheet using a form, the developers have to consider to the concurrent submission from the form. For example, when the multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such situation was investigated.

A very useful analysis to see how many form submissions can be handled in Google Sheets at the same time. The experiment looks at both submissions using Google Forms and as a Web App.

Source: Concurrent Writing to Google Spreadsheet using Form

Track Customer Subscriptions with Google Spreadsheet & App Script by sending an Email Automatically

Do you still spend time every day monitoring your sales report, identifying which customer subscription is going to expire soon, and manually draft an email to send out to your customer? Would it be better if this manual task can be automated every day for you? If that’s the case, this article can be a great help for you!

Following on from some recent Google Sheet payment solutions here’s a tutorial that highlights how you can use Sheets and Apps Script as a payment reminder tool. Combine with Stripe or Razorpay and you have a complete subscription management solution.

Source: Track Customer Subscriptions with Google Spreadsheet & App Script by sending an Email Automatically

How to Request Payments with Stripe Checkout and Google Sheets – Digital Inspiration

How to use Google Sheets to generate Stripe payment links and request payments in any currency from customers anywhere in the world!

We’ve previously featured Amit Agarwal’s Razorpay and Google Sheets payment solution. This latest example from Amit highlights how to integrate Stripe payments with Google Sheets. The source post contains the code you’ll need, how to setup and also automation options.

Source: How to Request Payments with Stripe Checkout and Google Sheets – Digital Inspiration

Create personal stock portfolio tracker with Google Sheets and Google Data Studio

Image credit: All Stacks Developer

The idea is to take advantage of what Google Sheets and Google Data Studio offer and pull them together to build a personal stock portfolio tracker.

This post provides an insight into how you can maximise the combination of Google Sheets, Data Studio and Apps Script to create powerful dashboards. The source link provides an overview of the solution and if you are interested in finding out more about how this solution works visit LION stock portfolio tracker guide (from there you can make a copy of the demo and see the code).

Source: Create personal stock portfolio tracker with Google Sheets and Google Data Studio

How to Create Automated Snapshots of your Google Sheet using Google Apps Script

In this video, we’ll show you how to automate snapshots of your Google Sheets and have those logs stored in a separate Google Drive folder so that you can keep your logs nicely organized for future reference. To do this, we’ll be using Google Apps Script to create a script that references the SpreadsheetApp library and the DriveApp library.

There seems to be an increasing number of YouTube channels dedicating content to highlight Google Apps Script solutions. Here’s the latest one we’ve come across from aguycalledjoe at bootstrapping.tools

Bulk convert Google Docs to PDFs

Bulk convert all the Google Docs within a given folder to PDFs and optionally delete the Docs afterwards. Specify the destination folder for the PDFs.

Bulk convert Google Docs to PDFs using Apps Script

Bulk convert Google Docs to PDFs using Apps Script

The following Google Apps Script tool is designed to bulk convert all Google Docs within a given Google Drive folder into PDFs. You can choose the destination folder for the PDFs to be put into and also whether you want the original Docs to be deleted. This tool does work on Shared drives.

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

Google Apps Script for Android Translation – Saravana Thiyagaraj

Normally we get strings.xml files from our translation team, but today was one of those days, where (due to time constraints) we got the source Google Sheet file. We had to generate strings.xml files out of these. This gave me the perfect opportunity to explore Google Apps Script which you could run on your Google Sheet.

This post highlights how Google Apps Script can quickly solve those little problems, in this case how to generate language .xml files used in Android. Follow the link to the source post for an explanation and code.

Source: Google Apps Script for Android Translation

How to Request Payments with Razorpay and Google Sheets – Digital Inspiration

How to use Google Sheets to generate Razorpay payment links and easily accept payments from customers anywhere in the world!

Razorpay is a popular payment gateway in India that allows you to accept online payments from customers anywhere in the world. Your customers can pay with credit cards, debit cards, Google Pay, Walmart’s PhonePe and other UPI apps.

A great solution from Amit Agarwal for creating payment links in Google Sheets for Razorpay. The source post provides more details of how this works and setup required. The post also outlines how you can combine this with a mail merge to sort all your invoicing needs.

Source: How to Request Payments with Razorpay and Google Sheets – Digital Inspiration

Get the redirect location of a URL using Google Apps Script

Have a list of shortened urls and need their final destination? Use this custom function in google sheets to extract them all at once.

Handy little snippet that can make testing redirects on URLs a lot easier, which can be useful for expanding short links from social media sites like Twitter.

Source: get the redirect location of a url using google apps script

Bulk create Google Drive Folders and add Files

Bulk create Google Drive folders with optional files copied into each one. Also optionally add edit permissions to these folders/files.

Bulk create Google Drive folders and add files, from a Sheet of data

Bulk create Google Drive folders and add files, from a Sheet of data

The following Google Apps Script tool was developed to bulk create Google Drive folders with optional files copied in to each one, all from data within a Google Sheet. There is also the option to add specific ‘edit’ permissions to the newly create Drive folders of which the files would automatically inherit this access level.

Key Functionality:

  • Complete the necessary information in the Config sheet before proceeding. Then use the Create folders option from the Admin menu at the top of the Google Sheet.
  • Adding permissions is optional – use the Config sheet to change the dropdown as required. If you select ‘No’ then the usual Google Drive inheritance will occur based on the parent Google Drive folder.
  • You can add multiple File IDs into the relevant cell and they can be different for each row. Leaving this cell blank/empty means no files will attempt to be copied into the new folder.
  • The original filename will be updated during the copy to append the folder name to the end of it, in order to prevent creating a large number of files with identical names.
  • There is a Log sheet to help troubleshoot any errors which may occur when running the tool.

Source: The Gift of Script: Bulk create Google Drive Folders and add Files

How to Build Custom Functions in Google Sheets with Google Apps Script

Build your own functions in Google Sheets that do anything you need them to in a few lines of code.

This is a great tutorial if you are interested in finding out more about developing custom functions in Google Sheets. The post includes everything you need to get started from handling single cell and range inputs, to documenting your function so that users see inline help.

Source: How to Build Custom Functions in Google Sheets with Google Apps Script

Mergo – Mail Merge for Gmail – A free lifetime subscription for Google Workspace for Education users

As teachers gear up for the new school year, we’re glad to offer a free lifetime subscription to your school, college or university. 🥳

I’m sure many of you have come across Romain Vialard who has been contributing to the Google Apps Script community since the product was in beta.

Mergo, Mail Merge for Gmail, is one of the latest products to be developed by Romain and for Google Workspace for Education users the great news is that until the 20 September 2021 there is an offer for a free lifetime subscription! Follow the source link for more details.

Source: Mergo – Mail Merge for Gmail – Back to school

Website Status Monitoring using Google Sheets [and Google Apps Script with optional notifications to Google Chat]

A Spreadsheet-bound apps script solution to conduct automated status monitoring on websites listed by the user in a Google Sheets management file. A separate status log file in Google Sheets will be created so that users can easily integrate data with BI services such as Google Data Studio. Notifications of changes in website status will be sent to the user’s Gmail. An optional setting to send notifications to Google Chat is available.

Nice example of using Google Chat for individual/group notification. The developer of this is also very responsive on Github if you encounter issues or have suggested changes.

Source: Website Status Monitoring using Google Sheets

Search Google Drive files and extract row data

Search and extract the content of Google Drive files. Collate the row data into a collective Google Sheet.

Search and extract the content of Google Drive files

Search and extract the content of Google Drive files

This was created for a specific Subject Access Request but may be of interest/use to others.

Source: The Gift of Script: Search Google Drive files and extract row data

Subscribe to Apps Script Pulse...