AppsScriptPulse

Sending data from a webcaptioner.com channel webhook to Google Sheets with Google Apps Script

push data from web captioner to google sheets via webhooks built using apps script.

the problem – playing with webcaptioner.com which lets you specify a webhook url. i was experimenting with adding captions to a google doc in realtime but apps script couldn’t keep up dropping ~20% of the captions sent via the hook

Sourabh Choraria kindly helped me solve an issue I was having passing data from webcaptioner.com to Google Sheets. Click through to the source link for the full explanation from Sourabh, but in essence the problem was I was losing data because I wasn’t using the LockService .

In the meantime webcaptioner.com have also updated there channel webhooks so you can set a data chuck size so if you are looking to capture a transcript in Google Docs you can use the following published as a container bound web app:

// @OnlyCurrentDoc
function doPost(e) {
  const params = JSON.parse(e.postData.contents);
  DocumentApp.getActiveDocument()
    .getBody()
    .appendParagraph(Utilities.formatDate(new Date(), "GMT", "HH:mm:ss")+': ' + params.transcript);
}

Source: web captioner, webhooks and google apps script

Templated Emails With Google Apps Script To Make Your Brand Team Happy

If your organization is using G-Suite (now known as Google Workspaces) then you’re hopefully using Google Apps Script to automate your email communication. (If you aren’t, why not? It’s a brilliant way to manage your communications.) You probably have different types of emails — for different purposes.

Do all of your emails have the same look-and-feel? Do they all match your organization’s branding requirements? How easy is it to update the format for all of them if you need to change a core/base element? Maybe the company tagline and color theme changed — how quickly can you get all of your emails updated?

I am going to show you how to create templated emails so that all of the emails you send have the same look-and-feel and are easy to maintain/update.

Source: Templated Emails With Google Apps Script To Make Your Brand Team Happy

How to Copy a Row to another Sheet with Google Apps Script – Learn how in this tutorial from @saperis_io

Tired of manually copying rows from one Google sheet to another? Automate it! I’ll show you how in my latest video tutorial.

For people just getting started in Apps Script nice little video tutorial showing how you can move rows in Google Sheets (this tutorial is the latest in a growing list of videos produced by Chanel Greco Founder & CEO of saperis).

Quickstart: Check student attendance in Google Meet from a Google Classroom roster using Google Apps Script

Complete the steps on this page to create a Google Sheets add-on that tracks attendance for Google Classroom courses delivered using Google Meet conferencing.

Nice spot via @barrielroberts/@alicekeeler for an official Google Apps Script quickstart which combines Google Sheets, Google Classroom and Admin Reports which lets you create an automated Google Meets attendance roster tool. The quickstart has all the code and instructions you need to get this working.

Source: Quickstart: Check student attendance in Google Meet courses

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

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

Sentiment Analysis For Google Tables Using Goole Apps Script – Ben Collins

Learn how to use Google Cloud’s Natural Language API to do sentiment analysis on tickets submitted to a Google Tables Support Issue Tracker. We’ll use Google Tables as the platform for our Support Tracker and Apps Script to connect to the Cloud Natural Language API.

Great example from Ben Collins building on the work of Alicia Williams which shows how you can interact with your Google Tables with Google Apps Script. As part of this solution Ben uses the new Tables Service, which is the latest Advanced Service to hit Google Apps Script, connecting to the Google Cloud Natural Language API. Click through to the source for all the details you need to get started.

Note: Google Tables beta is currently only available in the U.S.

Source: Sentiment Analysis For Google Tables Using Apps Script –

Using Google Apps Script to schedule Google Meets for a Virtual Parents’ Evening Assistant

If your school is considering conducting an online ‘Virtual Parents’ Evening’ due to covid restrictions, this project might save your staff time. The Google Sheet (make a copy at https://bit.ly/vpeassistant ) uses Google Apps Scripts to automatically create a Calendar event (in your default Calendar), create a Meet link as part of the event, paste the Meet link back into the Sheet and create a draft email to the parent containing the Meet link and time/date.

A simple and effective solution from Luke Craig to help you manage virtual parents evenings … and more. In particular useful to see how you can add Google Meet video calls to you calendar invites. The video description also includes a link to the source code on Github.

Google Drive Monitor – Get Email Alerts When Files are Deleted in your Drive with Google Apps Script – Digital Inspiration

Monitor your Google Drive automatically and get email alerts when important files and folders are deleted from your Google Drive. You can also watch file activity in Shared Drives

Source: Google Drive Monitor – Get Email Alerts When Files are Deleted in your Drive – Digital Inspiration

The Gift of Script: Search spreadsheet for multiple criteria

Search through rows of a Google Sheet simultaneously looking for certain criteria all in one single loop. I needed a way of doing this without multiple loops despite searching for separate information and here is a way of achieving that via true/false flags.

Rows of email address and role in spreadsheet

Screenshot of spreadsheet data to search

Source: The Gift of Script: Search spreadsheet for multiple criteria

Content generation with Google Workspace Add-ons

Google Workspace Add-ons

Image credit: Google

Back when G Suite, err… Google Workspace Add-ons launched, support for Docs, Sheets, and Slides was conspicuously absent. Sure, they’ve long supported their own flavor of add-ons, but the idea of building an add-on two different ways didn’t sit well with me. Thankfully I didn’t have to wait too long for the editors to catch up. … Now that add-ons work in the editors I had the chance to build a few small demos to try them out.

Recently it was announced that Google Workspace Add-ons now support the extension of the Editors (Docs, Sheets and Slides). Steven Bazyl (Google, DevRel) has shared an overview to help you get started with Workspace Add-ons. The examples Steven shares are particularly useful if you want to start developing add-ons that work across editors as well as in Gmail. This post is particularly useful for tips on handling different host applications. Follow the link to the source for all the code and more details…

Source: Content generation with Google Workspace Add-ons

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

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

Send Charts In Email Using Google Apps Script

… how to send Charts in Email using Google Apps Script. Sending charts on Email can be used to send the attendance charts of the students, sales chart for the week, share price charts, etc.

Always nice to see others in the Apps Script community use the work of others for inspiration and reshare the results. In this post latest post from Aryan Irani you can learn how to send charts from Google Sheets. This post covers a basic technique that you can easily expand on (if you are having issues with the charts rendered in your emails using this solution check out my referenced post for an alternative method).

Source: Send Charts In Email Using Google Apps Script

How to Make your Documents Read-only in Google Drive using Google Apps Script – Digital Inspiration

Your documents and files in Google Drive can be modified by anyone who has edit access to the file. You can always remove external collaborators from your documents to prevent them from editing your files but how do you prevent yourself (the owner) from accidentally editing your own files in Google Drive? Google Drive doesn’t have a simple button (yet) for locking files so here’s a little Google Script that can help you make any file in your Google Drive read-only.

Nice post from Amit Agarwal showing how the new Google Drive Locking API can be used with Google Apps Script. The post contains all the code and instructions you need to get started.

Source: How to Make your Documents Read-only in Google Drive – Digital Inspiration

DEV: How to Use Google Sheets as a Database (Responsibly)

Turn your spreadsheet into a database with examples for querying, inserting, updating, and deleting via API. In this guide. I’ll also explore the limitations of Google Sheets, including scalability, and where it makes sense to start looking at more complex alternatives.

Whilst not directly Google Apps Script related this post from Jacob Lee (Co-founder Autocode) provides lots of useful advice and tips on using Google Sheets as a database. The post also does an excellent job of highlighting the hard limits as well as usability issues before you hit those limits.

Source: How to Use Google Sheets as a Database (Responsibly)

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

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

Subscribe to Apps Script Pulse...