AppsScriptPulse

Solutions, Calendar, Meet, Sheets, Beginner

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

Tags:
| | 0 Comments |

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.

Snippet, Beginner, Sheets

The Gift of Script: Search spreadsheet for multiple criteria

Tags:
| | 0 Comments |

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

Add-on, Solutions, Docs, Gmail, Sheets, Slides, Intermediate

Content generation with Google Workspace Add-ons

Tags:
| | 0 Comments |

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

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

Send Charts In Email Using Google Apps Script

Tags:
| | 0 Comments |

… 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

Solutions, Sheets

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

Tags:
| | 0 Comments |

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)

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, Docs, Forms, Sheets, V8, Beginner

Create PDF from a Google Form submission using Google Apps Script

Tags:
| | 0 Comments |

In this blog post , we will cover how to create a PDF from the responses of Google Form using Google Apps Script.

If you are looking for some guidance on creating and sending PDF Documents from Google Form responses this post has all you need to get started.

Source: Create PDF Using GoogleApps Script

Solutions, Sheets, V8

Working with Pivot Tables in Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

Pivot tables are a quick and powerful way to analyse data. Using Google Apps Script, there is an ability to build and modify pivot tables in Google Sheets. In this post I’ll share some tips and tricks for interacting with pivot tables in Google Sheets.

Source: Working with Pivot Tables in Google Sheets using Google Apps Script

Solutions, Sheets, V8

Benchmark: Process Costs for Searching Values with TextFinder in Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

Here, I would like to report the process costs for searching values in Spreadsheet using Google Apps Script (GAS). When the values are searched in Google Spreadsheet, the following 3 patterns can be considered. Ref

  1. Retrieve all values using getValues, and the values are searched from the retrieved array.
  2. Use TextFinder.
  3. Use Query language.

TextFinder is a relatively new addition to Google Apps Script with was released on April 5, 2019. TextFinder is part of the Spreadsheet Service and provides methods for finding or replacing text within a range, sheet or spreadsheet. There have been some reports on Stack Overflow that the TextFinder methods can be relatively slow to execute compared to using native JavaScript techniques. This post by Tanaike explores the process time of TextFinder against other techniques. Click through to the source for the results and code for running the experiment.

Source: Benchmark: Process Costs for Searching Values in Spreadsheet using Google Apps Script