AppsScriptPulse

Google Apps Script – What a difference 5 years make – Episode 2 [Property Service and Spreadsheet Service updates] – Desktop Liberation

Image credit: Bruce Mcpherson

Image credit: Bruce Mcpherson

How google apps script has changed over the past 5 years. Episode 2 in a series of post covers the Properties Service and the Spreadsheet service

Bruce Mcpherson continues his look at changes in Google Apps Script over the last 5 years, this time looking at PropertiesService and SpreadsheetApp . This post is worth a read just to check you’ve not missed any new methods for working with Google Sheets, because over the last 5 years there have been many!

Source: Google Apps Script – What a difference 5 years make – Episode 2 – Desktop Liberation

Telegram Bot with Inline Keyboard in Google Sheets [and Google Apps Script]

Questa funzionalità permette di visualizzare, insieme al messaggio del bot, una tastiera virtuale con una serie di bottoni dove gli basterà cliccare su uno di essi per scegliere la sua risposta

[Google Translate: This feature allows you to view, together with the message of the bot, a virtual keyboard with a series of buttons where it will be enough to click on one of them to choose its response]

The accompanying video and post for this solution are in Italian and Google Translate might be required. This is a great resource and another example of a high quality video produced by the Apps Script community. The solution provides an overview of how you can display an inline keyboard within a Telegram Bot using a single Google Apps Script project.

Source: Telegram Bot with Inline Keyboard in Google Sheets

Get the difference between dates in minutes

The following Google Apps Script is designed to get the difference (in minutes) between 2 dates from a Google Sheet. This was part of a tool used to create events from data in a Google Sheet where I needed to get the duration of the meeting for Zoom. The actual date, hour and minute values are separated in columns as it was easier to control user input in that format, so we will need to piece them together.

Start and End date values in a Google Sheet

Start and End date values in a Google Sheet

Source: The Gift of Script: Get the difference between dates in minutes

Create Filters in Google Sheet using Google Apps Script

How to create filters in Google Sheets using Google Apps Script.

There are lots of ways you can filter data in Google Sheets and in this post Aryan Irani uses SpreadsheetApp.newFilterCriteria() to create a filter and then copy the filtered data to a new sheet. The post contains everything you need to know to filter data in this way.

Source: Create Filters in Google Sheet using Google Apps Script

Automate Google Sheet Reporting in 5 minutes

Have you ever collect data with google sheets where the sheets are shared with everyone in your organization for inputting their weekly, monthly data such as performance reporting, inventory reporting, or KPI data reporting? How good if these repetitive actions can be automated and the final report can be emailed out to respective people (such as your boss) every month? Yes, this article can help you to achieve this target!

This is a nice tutorial if you are looking for a quick way to send Google Sheets charts by email.

Source: Automate Google Sheet Reporting in 5 minutes

Create a simple Approval process via Email with Google Form and Google Apps Script (Web Apps Deployment)

At the end of this article, you will know how to create a workflow app, based on a Google Form that send an email to be approved or deny including logging of who is approving or denying with Apps Script.

Great post from Jérémy Dessalines who has put together this tutorial for a custom workflow using a Apps Script Web App. The post covers triggers, the publication process and how to generate a unique ID and includes some great tips at the end.

Source: Create a simple Approval process via Email with Google Form and Google Apps Script (Web Apps…

Find and replace in a Google Sheets with Google Apps Script – whole data search

This solution cleanse Google Sheet data by searching for specific words and replacing them with an alternative. As part of this the solution highlights cell changes with an alternative colour. This example uses ‘TextFinder’ rather than ‘For Loops’.

Use the 'Welcome' sheet for input to run the tool

Use the ‘Welcome’ sheet for input to run the tool

Source: The Gift of Script: Find and replace in a Google Sheet – whole data search

Bulk create Google Drive folders with a sub-folder

Bulk create Google Drive folders with a sub-folder from data given in a Google Sheet. Optional user permissions can be added.

Bulk create Google Drive folders from a Sheet of data

Bulk create Google Drive folders from a Sheet of data

This tool and blog post is largely a continuation of this one for bulk creating Google Drive folders. The main difference being that there is an extra column to provide the name of your sub-folder. Note that the tool will try to create a sub-folder regardless of what is entered – as I was just aiming to create a version for the many requests I got from the previous blog post from people who wanted a sub-folder.

Source: The Gift of Script: Bulk create Google Drive folders with a sub-folder

Parse 16K URLs and Put the Result to Google Sheet in 3 Minutes

Photo by Joel Mott on Unsplash

Sometimes my best option is to write code on the go rather than using libraries or searching for ready-to-use code. I’ve made a short script for parsing URLs and putting the result into my Google Sheet.

As a developer it is always interesting to see how other people approach problems. This little snippet from Max Makhrov caught my eye because the approach to parsing data in a Google Sheet wasn’t one I’d seen before. Follow the source link to have a look yourself…

Source: Parse 16K URLs and Put the Result to Google Sheet in 3 Minutes

Find and replace in a Google Sheet

Cleanse Google Sheet data by searching for specific words in a columns and replacing them with an alternative. Colour any cell changes to highlight. This is one of many ways of performing the task simply using loops.

A list of words to find and their replacement equivalent.

A list of words to find and their replacement equivalent.

Source: The Gift of Script: Find and replace in a Google Sheet

Google Apps Script Example: Automated YouTube Stats Workflow

Extract a list of Google Group members into a Sheet with Google Apps Script

Google Groups

Image: Google

Extract a list of members of a Google Group (email address and role) into a Google Sheet. You simply enter the email address of the Google Group that you belong to and then run the Function.

Source: The Gift of Script: Extract a list of Google Group members into a Sheet

Visually display status when looping through Google Sheets data with Google Apps Script

show a processing status in a cell when looping through rows in google sheets using google apps script.

When working with small batches of data it can be useful to show the user that the data is being processed. Here is an example script from Sourabh Choraria which use SpreadsheetApp.flush() to force write updates to a Google Sheet. As this method relies on .setValue() instead of .setValues() it’s worth keeping in mind for bigger data sets that using HTMLService and a custom dialog or Speadsheet.toast()

If the .setValue() / .flush() Al from coda has provided this gist

Source: visually display status when looping through google sheets data

How to Unsubscribe from Mailing Lists and Junk Newsletters in Gmail with Google Apps Script – Digital Inspiration

Christian Heilmann’s tweet – Feature request for Gmail: automatically find and follow the unsubscribe link in all highlighted emails – prompted me to build an automated system for unsubscribing your Gmail address from the bulk senders. Here’s how it looks…

This is an older example from Amit Labnol that recently pinged my Google Alerts from this Best Unroll.me Alternatives post. The source post from Amit has some nice details about how marketing emails are marked up and the code shared might be a nice starter for your own solution.

Source: How to Unsubscribe from Mailing Lists and Junk Newsletters in Gmail – Digital Inspiration

Replace text in Google Spreadsheets with Apps Script using TextFinder

Google Sheets has a powerful find and replace function. But you may want to use Google Apps Script to do the same job. StackOverflow has a question on this. The accepted answer would require you to loop through all cells, find the text, replace it and post the new text back to the cell. Google Apps Script has a more powerful, and simpler, method called TextFinder .

Usual post highlighting the TextFinder method which you can use to find and replace text in Google Sheets. The post has more information about this method and how to use it.

Source: Replace text in Google Spreadsheets with Apps Script

Subscribe to Apps Script Pulse...