AppsScriptPulse

Add-on, Solutions, Drive, V8, Intermediate

Creates a Google Workspace Add-on file picker card with CardService that opens a Google Picker in an overlay window – Google Apps Script – Yagisanatode

Tags:
| | 0 Comments |

So you have this awesome idea for a Google Workspace Add-on (GWAO), but you need to be able to select Google Drive files and folders as a part of your process. … Ideally, you would want a built-in File Picker class that would select the files and folders from the directories you need. Whelp… unfortunately, we don’t have that right now for Google Apps Script’s Card Service.

One approach might be to build out a file picker card selecting each parent’s files and folders and navigate through it like, say, a linked list. … Instead, I decided to incorporate Googles File Picker API as a popup window from the sidebar, because, it’s kinda what it is designed for.

The Google Apps Script GDEs are publishing some very thorough tutorials just now. The latest comes from Scott Donald who has published a tutorial exploring how the Google Drive Picker can be used with Workspace Add-ons that use the Card Service. The post is particularly good at highlighting the steps required as a Google Cloud Console project.

Source: Creates a Google Workspace Add-on file picker card with CardService that opens a Google Picker in an overlay window – Google Apps Script – Yagisanatode

Solutions, Docs, V8, Beginner

How to find and replace text in a Google Doc with a link or a list of links with Google Apps Script – Yagisanatode

Tags:
| | 0 Comments |

Learn how to find and replace text with a link in Google Docs with Google Apps Script Document App with 3 different scenarios.

So you are a citizen Google Apps Script developer and you’ve decided to make yourself a mail-merge-type project where you want to create new documents from a template. … Now you want to take it to the next level and replace the text with a hyperlink containing the text and the URL. You might be scratching your head wondering where the replaceTextWithLink() method is or why you can’t simply chain the setLinkUrl() method without making a hyperlink out of the entire body of the document.

Another one of Scott Donald’s very thorough tutorials, this one exploring link creation in Google Docs.

Source: How to find and replace text in a Google Doc with a link or a list of links with Google Apps Script – Yagisanatode

Solutions, Forms, Gmail, V8, Beginner

How to Create a Custom Email Auto-responder for a Google Forms Submission with Google Apps Script – Yagisanatode

Tags:
| | 0 Comments |

A few days before publishing this post, I put a call out for some Beta testers to get free access to my new course Google Sheet: Learn the Essentials through 3 Detailed Projects.

I needed a way to provide coupon codes for free access to the course in exchange for some feedback and much-needed tutorials.

To do this, I created a Google Form.  This contained some details and expectations, and then some details about the submitter and a consent checkbox at the end. If the submitter consented and hit submit I wanted them to get an email back with the coupon code.

In this tutorial, we will go through how to create a custom auto email response containing:

  • The submitter’s name in the greeting.
  • Your email message.
  • Your primary signature block from your Gmail account.

Source: How to Create a Custom Email Auto-responder for a Google Forms Submission with Google Apps Script – Yagisanatode

Solutions, V8, Intermediate

How to programmatically schedule weekly time triggers between two dates in Google Apps Script – Yagisanatode

Tags:
| | 0 Comments |

A tool to schedule weekly time triggers between two dates in Google Apps Script. Grab the code and ready the quick-use guide or stick around for a deep dive.

Nice solution from Scott Donald to setup timed triggers for a fixed period so you don’t have to worry about manually adding/removing.

Source: How to programmatically schedule weekly time triggers between two dates in Google Apps Script – Yagisanatode

Gotchas, Solutions, Beginner

Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone. – Yagisanatode

Tags:
| | 0 Comments |

If you have made it to this post you are probably a little frustrates about why your Google Apps Script time triggers are not running on or around the times that you have designated … To cut to the chase, the likely cause of clock triggers not running in the time range that you have selected is that the time zone in your Google Apps Script project is out.

Scott ‘Yagi’ Donald provides some very useful tips on how you can check and adjust your Apps Script project’s timezone.

Source: Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone. – Yagisanatode

Solutions, Web App, V8, Intermediate

Google Apps Script: Store a Unique User Key from a User Accessing your WebApp – Yagisanatode

Tags:
| | 0 Comments |

Google Apps Script: WebApp In this tutorial, we will cover how you can get a unique temporary access key from a user accessing your WebApp that lasts for 30 days. Temporary access keys allow you to track users as they use your WebApp over time while still providing anonymity to the user by providing only an access key to that user.

Source: Google Apps Script: Store a Unique User Key from a User Accessing your WebApp – Yagisanatode

Solutions, Sheets, V8, Beginner

Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script – Yagisanatode

Tags:
| | 0 Comments |

In this tutorial, we will cover creating a custom Table of Contents that lists your Google Sheets tabs on its own tab using Google Apps Script. This Table of Contents (TOC) will update whenever you open your sheet or choose to update it with a button.

As always a very detailed explanation of how this solution works from Scott ‘Yagi’ Donald.

Source: Create a custom automated Table of Contents for your Google Sheets tabs with Google Apps Script – Yagisanatode

Solutions, Forms, Sheets, V8, Beginner

Create custom prefilled Google Forms links in custom emails with Google Apps Script – Yagisanatode

Tags:
| | 0 Comments |

Recently I raised a support ticket with a tech company I was subscribed to where we were trying to resolve an integration issue I had with their service. Once we had it all resolved they followed up with a feedback form. That feedback form just happened to be a Google Form.

In this tutorial, I’ll walk you through accessing the prefill tool in Google Forms. Then, if you are keen on doing some coding, we’ll create a little custom feedback form for unique users that we will deliver via email.

Nice tutorial from Scott ‘Yagi’ Donald on using prefilled Google Form links. For beginners the post includes everything you need to know. For more experienced developers Scott includes some nice code for handling Google Sheet data.

Source: Create custom prefilled Google Forms links in custom emails with Google Apps Script – Yagisanatode

Solutions, Sheets, Web App, V8, Advanced

Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets – Yagisanatode

Tags:
| | 0 Comments |

In this tutorial, we are going to create an interactive story chain app that we can embed into a Google Site or your own site like WordPress.

Yagi uses a simple but very effective interactive story chain app to explore and explain some very powerful Apps Script functionality all developers should know about. This includes using the LockService which can be used to prevent concurrent access to sections of code. Yagi also highlights some recent discussions around the limitations of Web Apps and concurrent users. Click through to the source for all this and more.

Source: Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets – Yagisanatode

Solutions, Sheets, V8, Advanced

Creating Unique Ranges from 2D Arrays in Google Apps Script – Yagisanatode

Tags:
| | 0 Comments |

If you have ever done any work in Google Sheets you might have come across the incredibly useful UNIQUE function. This function can take a single row or multiple rows and generate a list of unique cell values.

Sometime you might have a use case where you need to replicate the UNIQUE functionality in Google Apps Script. … Whatever the case, knowing how to to create a unique array from a 1-dimensional array or a 2-dimensional array is an important part of your toolkit as a coding goat 🐐.

This tutorial will look at how to create this with modern Javascript that is incorporated into the Google Apps Script language.

Source: Creating Unique Ranges from 2D Arrays in Google Apps Script – Yagisanatode

Solutions, Sheets, Intermediate

Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data – Yagisanatode

Tags:
| | 0 Comments |

Google Apps Script, Google Sheets, Javascript Have you ever wanted to have a Google Sheet available for only those users who need to edit and then once they are done, take their edit permissions away to maintain the integrity of the sheet? I know that I have come across these conditions a number of times … Continue reading “Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data”

Source: Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data – Yagisanatode

Solutions, Sheets, V8, Intermediate

Google Apps Script: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times – Yagisanatode

Tags:
| | 0 Comments |

Imagine if you have a set of data, say, Cat, Dog, Goat and you want to create a new column where each item is repeated 3 times. Essentially what we are doing here is spreading out our original … There is no real easy approach to do these things with the standard Google Sheets Formulas (Known as Functions in Google Sheets). Fortunately, we can create custom functions with Google Apps Script to do the job.

In this post, I will go over the 3 custom functions that I created to solve these three problems. I’ll then provide a quick-use guide if you just want to dive in and get started using them. Finally, I go over some of the interesting parts of the Google Apps Script code for those of you who wish to learn more about creating your own custom functions.

Source: Google Apps Script: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times – Yagisanatode

Solutions, Sheets, Slides, V8

Google Apps Script: Automated PDF Certificate Creator That Is Emailed to the Attendee – Yagisanatode

Tags:
| | 0 Comments |

Use Google Apps Script, Google Slides and Google Sheets to quickly automate creating a Certificate of Attendance and send it to attendees.

Nice example using Google Apps Script to generate and share .pdf documents via email. The source has all the code you need to get going on this.

Source: Google Apps Script: Automated PDF Certificated Creator That Is Emailed to the Attendee – Yagisanatode

Solutions, Drive, Sheets, V8

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet – Yagisanatode

Tags:
| | 0 Comments |

Have you ever asked for a list from a client, another department or agency and instead of saving into a file that might even be considered marginally useful, they give it to you as a pdf. … Continue reading “Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet”

Yagi shares a comprehensive walk-through of how you can sometimes extract data from .PDF documents using Google Apps Script and the advance Drive service. The solution is also able to work on .PDFs created from images using the built-in OCR features of Google Drive and you might want to drop the getTextFromPDF() function shared in the post.

Source: Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet – Yagisanatode

Classroom, Sheets

Google Apps Script: Upload grades into a Google Classroom Coursework Assignment – Yagisanatode

Tags:
| | 0 Comments |

Google Classroom has become one of the darlings of the teaching world that has come out of this crisis. Its shallow learning curve and attractive UI made it an obvious choice for most educators. Oh, and I am sure the fact that it is free helped too.
One of the limitations is that you cannot simply upload grades from, say Google Sheets or another assessment tool. Well…not directly in the Google Classroom platform, yet.

This tutorial runs through the basics of uploading grades from Google Sheets into Google Classroom using Google Apps Script with the Classroom API.

Source: Google Apps Script: Upload grades into a Google Classroom Coursework Assignment – Yagisanatode

Solutions, Docs

Google Apps Script: Get Google Doc Text Based on Reference Characters – Yagisanatode

Tags:
| | 0 Comments |

This simple tutorial should get you started with grabbing text from a Google Doc for other uses. You could certainly make a lot of changes to the code to introduce more search rules. Likewise, you could use regular expressions to search for some really cool stuff, but for a simple overview this should get you started.

This post provides a detailed how-to of getting a list of information from the document that you have noticed are between two sets of characters.

Source: Google Apps Script: Get Google Doc Text Based on Reference Characters – Yagisanatode

Solutions, Sheets

Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph) – Yagisanatode

Tags:
| | 0 Comments |

Have you ever woken up in the middle of the night thinking – phawh! I’d really like to know how many words there are in a paragraph – Or stopped mid-chew on some arbitrary item carelessly left in the paddock by the boss and thought – geez! If there was only a way to count the number of sentences in a paragraph in a cell in Google Sheets!?

In this short tutorial, Yagisanatode will show you how to get the total count of words in a cell and the total count of sentences in a cell in Google Sheets. They even have a how-to on getting the average sentence length in a paragraph.

Source: Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph) – Yagisanatode

Solutions, Sheets

Google Apps Script: Add and removed Google Sheets columns based on a search array

Tags:
| | 0 Comments |

Have you ever wanted to delete or add columns in a Google Sheet, based on another set of Sheet data?

In this tutorial, we’ll show you how to use Google Apps Script to update your headers based on another sheets values. These sheets values can come from the current Google Sheet workbook or another one. We will also ensure that the data below the headers is migrated along with the new header location.

Source: Google Apps Script: Add and removed Google Sheets columns based on a search array

Solutions, Sheets

Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets – Yagisanatode

Tags:
| | 0 Comments |

This course requires a rudimentary understanding of very basic Javascript concepts and of what an array is. Nevertheless, if you want to come in blind you should be able to pick things up with a bit of Googling.

One of the major regular tasks that Google Apps Scripts can do for Google Sheets is to automate the transformation of data from one state to another.

Sometimes this task can simply be accomplished by building dynamic templates using the vast functionality of Google Sheets. Other times automation via Google Apps Script is a better tool for the job.

Source: Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets – Yagisanatode

Solutions, Sheets

Filling out your column data to match the number of columns in a range – Yagisanatode

Tags:
| | 0 Comments |

Have you ever experienced the warning about “number of columns in the data does not match the number of columns in the range” when trying to set values or paste a range of values back into your Google Sheet with Google Apps Script?

Check out this post to see how this script can help you fill out your column data to match the number of columns in a range.

Source: Google Apps Script: Filling out your column data to match the number of columns in a range – Yagisanatode

Library

Working with Google Apps Script in Visual Studio Code using clasp – Yagisanatode

Tags:
| | 0 Comments |

This little tutorial is going to guide us through the steps that will allow us to work, push and pull Google Apps Script code from the online editor to Visual Studio Code.

Related to the clasp CLI from Google, this short guide gets you set up with clasp, installing an auto-completion library, and pushing a project from VS Code to your Apps Script project online.

Source: Working with Google Apps Script in Visual Studio Code using clasp – Yagisanatode

Solutions, Forms, Sheets

Create a seat booking form with Google Forms, Google Sheets and Google Apps Script – Yagisanatode

Tags:
| | 0 Comments |

In this tutorial, Yagisanatode (@LifeOfSpy) is going to build a simple seat booking Google Form that will contain a registeree’s name and the session that they wish to attend. Each time a registeree submits a request to book a seat for a session, that seat is taken from the list displaying only the remaining seats for that session for when the next user submits the form.

Source: Create a seat booking form with Google Forms, Google Sheets and Google Apps Script – Yagisanatode

Solutions, Sheets

Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets – Yagisanatode

Tags:
| | 0 Comments |

Have you ever created a Google Sheet projects where you could really use a select all checkbox (they call them ‘Tick-boxes’ in Google Sheets)? Sure you can copy a range of … Continue reading

Great post not only showing you how to make a ‘select all’ checkbox but you also get an in depth explanation of how this works. Once you fishish this post you might want to read this other Yagisanatode on How to check if there is a Tick Box (Check Box) in a cell or range

Source: Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets – Yagisanatode

Solutions, Calendar

Google Apps Script: How force subscribe Google Group Members to a Google Calendar for G Suite – Yagisanatode

Tags:
| | 0 Comments |

Use Google Apps Script to get all the emails addresses of the members in a G Suite group, compare that against all the current members in the Calendar and add anyone who is missing

Google Apps Script, G Suite Admins, Google Calendar, Calendar API, AdminDirectory, GroupsApp Note! This article is for G Suite users and admins. You won’t be able to apply the same code to Consumer accounts. Google Groups in G Suite can be a really convenient and clean way to share users to drive folders and file locations, … Continue reading “Google Apps Script: How force subscribe Google Group Members to a Google Calendar for G Suite”

 

Source: Google Apps Script: How force subscribe Google Group Members to a Google Calendar for G Suite – Yagisanatode

Solutions, Docs, Forms, Sheets, Slides

Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides – Yagisanatode

Tags:
| | 0 Comments |

 

I wanted to update one of my free Google Add-on apps that works with colour. What I had is just the standard HTML color input element where the user selects from the palette and that hexadecimal colour code … had always wanted to create a tool for a sidebar or dialogue box that would allow the user to easily access the standard colours or use the custom palette provided by the HTML color input”

Continue reading this post to find out how you can implement your own copy of the colour picker

Source: Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides – Yagisanatode

Subscribe to Apps Script Pulse...