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

Random and fake test data in Sheets with Google Apps Script – Desktop Liberation

Image credit: Bruce Mcpherson

Image credit: Bruce Mcpherson

Sometimes you need to generate some fake data for a spreadsheet. In this post I’ll cover a few utilities in 2 separate libraries that can help with this.

Bruce Mcpherson has put together a Google Apps Script library which is a port of Faker.js and elements of d3.js to make it easier to generate random data in your projects. You can see the full list of Faker.js methods which includes a wide range from names/addresses to dates to just lorem text. The library can also generate various fake datasets, strings and selectors.

Source: Random and fake test data in Sheets with Google Apps Script – Desktop Liberation

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

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

Google Apps Script – What a difference 5 years make – Desktop Liberation

It’s been over 5 years since my book ‘Going Gas‘ was published, and Apps Script evolution means that a number of sections of it is now pretty out of date. I don’t think I have the energy to do a rewrite – so I’m going to do a critique, chapter by chapter, of where it’s now wrong as a way to show how much Apps Script has improved (or otherewise) over the years.

We recently highlighted how you could preview excerpts from Bruce Mcpherson’s ‘Going GAS’ book. Bruce has now followed up by using the book contents from 2016 to highlight differences in the current version of Google Apps Script.

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

Going GAS – Book preview on Google Books

Image credit: O'Reilly

Whether you’re moving from Microsoft Office to Google Docs or simply want to learn how to automate Docs with Google Apps Script, this practical guide shows you by example how to work with each of the major Apps Script services.

Bruce Mcpherson the author of Going Gas has recently highlighted in the Google Apps Script Community that a chuck of his book is currently available for free in the Google Books preview (133 of the 456 pages are available). Bruce highlights that since publication in 2016 there have been a number of changes in Google Apps Script most notably the move to modern JavaScript syntax in the V8 engine, but much of the content may still be of interest.

Source: Going GAS

Google Area 120 Tables Beta joins Google Cloud opening up access for all!

Image: Google Area 120

Last September, Tables launched in Area 120, Google’s internal incubator for experimental projects, with the goal of proving market demand for a solution to help teams organize and track work, and it was a success! Google Cloud has committed to investing in this product area long-term. Moving forward, the beta version of Tables will still be available until we release a fully-supported Google Cloud product – which we expect to complete in the next year.

If you are not familiar with the Tables Beta it is a collaborative data platform designed to make it easier for no/low coders to create workflows and automations. If you would like to see the Tables beta in action Co-founder & Product Lead, Carlin Yuen, joined us on Totally Unscripted in New workflow solutions with Tables and Google Apps Script.The Tables beta has so far been limited to users in the US. The source link provides more details about timelines and how non-US users can join the beta. [HT Ivan Kutil]

Source: Tables Beta Update & FAQ

Simple and maintainable error-handling in TypeScript

Sometimes things fail — that’s a fact of life and programming. So as a programmer, you’re going to have to write error-handling code. Thankfully TypeScript has some handy features which can help us to create simple and maintainable error-handling code.

James Elderfield recently joined us on Totally Unscripted to share some insights into the infrastructure used at Supermetrics used to support the development of their Google Sheets Add-on and Data Studio Connector. As part of their stack James highlighted how they used TypeScript to write code complied to use in Google Apps Script. This post isn’t Apps Script specific but provides some useful tips on using TypeScript for error handling.

Source: Simple and maintainable error-handling in TypeScript

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

Totally Unscripted: Developing a market leading Google Workspace Add-on – Behind the scenes @Supermetrics Thur 10 June at 1200 PDT / 1500 EDT / 2000 GMT+1

Over half a million people, including marketers, data analysts, and data engineers, use Supermetrics to move data from popular marketing platforms (such as Facebook, Google Ads, and HubSpot) to Google Sheets, Google Data Studio and other destinations. With 10% of global online ad spend reported through Supermetrics and a growing distributed product engineering team, management and testing of their Apps Script deployments is key. In this episode of Totally Unscripted we speak to Supermetrics founder, CEO (and Apps Script developer), Mikael Thuneberg, and Supermetrics senior software developer James Elderfield to find out about Supermetrics development infrastructure and approaches to testing and monitoring.

Find out how to developing a market leading Google Workspace Add-on at 1200 PDT / 1500 EDT / 2000 GMT+1 on Thursday 10 June 2021 and tune in live to join the conversation with our guest!

For previous episodes, information and resources visit https://tu.appsscript.info/

Google App Script Properties and Cache Services

Properties and Cache

Video showing the Properties and Cache Services available for Google Apps Script

  • The Properties and Cache Services are designed to hold data for use beyond a single run of a script.
  • The Legacy IDE had an option to see view and directly interact with the Script Properties. This is not available in the new IDE but the service still exists and can be interacted with via code.
  • The primary difference between the PropertiesService and CacheService is that key-value pairs in the Cache are automatically deleted after 25 minutes, while data in the PropertiesService persist until deleted via code (or manually from the Legacy IDE)

Interesting to see a growing number of video tutorials being produced by the App Script community. The latest we’ve come across comes from Spencer Farris who in this post provides an overview of the Properties and Cache service. Click through to the source post to watch the video and access additional resources.

Source: Google App Script Properties and Cache Services

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

How to Schedule a Google Meeting with Google Calendar and Apps Script – Digital Inspiration

This Apps Script sample shows how you can programmatically schedule video meetings inside Google Meet with one or more participants using the Google Calendar API. It can be useful for teachers who wish to schedule regular meetings with their students but instead of manually creating meeting invites, they can easily automate the whole process for the entire class.

Source: How to Schedule a Google Meeting with Google Calendar and Apps Script – Digital Inspiration

Apps Script – How to track library and script usage – Desktop Liberation

You’ve written a great Apps Script library and you want to know how many people are using it, and perhaps even which parts of the library they are using, and how often. Perhaps you have a new version out, and need to know whether anybody is still using the old version. You may also have a set of libraries and you want to know whether users have flipped to the new version. All these things are important to know, but impossible to find out using the Apps script platform as is.

Bruce Mcpherson provides a detailed solution for tracking Google Apps Script library usage using the Properties Service. The source post contains everything you need to integrate this solution into your existing libraries as well as a quick way to chart usage.

Source: Apps Script – How to track library and script usage – Desktop Liberation

Totally Unscripted: Developing Google Workspace workflows with AppSheet Automation Thur 27 May at 1200 PDT / 1500 EDT / 2000 GMT+1

We are back and for the next episode of Totally Unscripted revisiting Google no/low code application development platform AppSheet. We are joined by Christian Schalk (Developer Advocate, Google Cloud) to look at the new AppSheet Automation, which uses Google AI to make it easier to automate business processes.

By making it easier to automate processes, AppSheet Automation helps users reduce IT overheads and save money. Automation also includes the ability to build and embed rich AppSheet views in Gmail to perform approvals on the go.

Tune in live to find out everything a Google Workspace developer needs to know about this and other new features in AppSheet.

For previous episodes, information and resources visit https://tu.appsscript.info/

Google Apps Script powered extensions, add-ons, and customizations for the Habitica self-improvement web app

Habitica is a self-improvement web application with game mechanics overlaid in order to help the player keep track of and remain motivated to achieve their goals.

The game is laid out in the form of an RPG, in which the player collects items such as gold and armor to become more powerful. Rewards are achieved through maintaining real-life goals, in the form of Habits, Dailies, and To-Dos.

Wikipedia

Can’t say that I’ve come across the self-improvement web app Habitica before, but good to know there is a long list of Google Apps Script powered extensions, add-ons, and customizations.

Source: Extensions, Add-Ons, and Customizations – Habitica Wiki

Google I/O AMA: Integrate Google Workspace with your solutions – 20 May 2021 at 2000UTC

Join this Ask Me Anything Session to get answers from the Google Workspace Development Platform and Google Workspace Developer Relations teams about how to integrate with your own solutions.

As part of Google I/O several of the Google team are hosting a Google Workspace developers ‘ask me anything’ session. The session panel includes Matt Izatt (Google Workspace Product Manager), Olaf Hubel (Google Workspace DevRel Manage), Charles Maxson (Google Developer Advocate and Totally Unscripted co-host) and Steven Bazyl Google Workspace Developer Relations Engineer. Follow the link to sign-up and follow the session.

Source: Google I/O 2021

How to Use Notion with Gmail and Google Sheets using Apps Script – Digital Inspiration

How to use the Notion API with Google Apps Script to connect Gmail, Google Forms, and Google Sheets with your Notion workspace.

Notion, my absolute favorite tool for storing all sorts of things from web pages to code snippets to recipes, just got better. They’ve released a public API and thus it will be a lot easier for developers to read and write to their Notion workspace from external apps.

For note keeping fans of Notion Amit Agarwal shares some basic code to interact with the Notion API. Amit also has wrapped this into a new private beta Gmail Add-on that you can see in the post with a link to request early access.

Source: How to Use Notion with Gmail and Google Sheets using Apps Script – Digital Inspiration

How to Use JavaScript to Automate SEO (With Scripts)

JavaScript’s developments – from web dev to machine learning – make it a perfect language to learn for SEO. Here’s what you need to know.

“Apps Script may offer a less intimidating way to learn to code because you can use it in apps like Google Sheets which are the bread and butter of technical SEO”

This is a more general post highlighting the benefits of learning Javascript particularly if you work in the field of SEO. Included is a shout out to Google Apps Script and links to some examples from the SEO/Apps Script community, which if you’ve not seen is worth checking out.

Source: How to Use JavaScript to Automate SEO (With Scripts)

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…

Using UrlFetchApp.fetch vs. fetchAll in Google Apps Script

Make multiple, asynchronous API calls simultaneously using UrlFetchApp.fetchAll in Apps Script. mostly when running through a list of resources against which an API call needs to be triggered, we end up using the UrlFetchApp ‘s fetch service; however, in case if the list is well-defined and needs to be consumed as a whole, you could make use of the fetchall service instead.

In this post sourabh choraria highlights the benefits of UrlFetchApp.fetchAll() when making asynchronous API calls in Google Apps Script.

Source: using UrlFetchApp.fetch vs. fetchAll in google apps script

Totally Unscripted: Managing Google Apps Script applications in large enterprise environments Thur 13 May at 1200 PDT / 1500 EDT / 2000 GMT+1

Totally Unscripted is back on the 13 May at the usual time (1200 PDT / 1500 EDT / 2000 GMT+1) and in this episode we look at Managing Google Apps Script applications in large enterprise environments. In this episode we are joined by Niek Waarbroek (CTO & Partner at NextNovate) and Jasper Duizendstra (Google Cloud Architect at duizendstra.com) who will share their approaches for managing the design, development and support process for the applications they build for their customers. As part of this show Jasper will also demonstrate his fully serverless, continuous delivery pipeline based on the Cloud IDE, Cloud Source Repositories, Cloud Build, git and clasp.

You can tune in live to ask questions via https://youtu.be/N5zmeEsG2uw

For previous episodes, information and resources visit https://tu.appsscript.info/

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

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

Get Email Alerts When COVID-19 Vaccines Become Available Near You [in India] – Digital Inspiration

Monitor the availability of Covid-19 Vaccines in your city and get email alerts when vaccine doses are available for people above 18 years of age.

It is very concerning to see the current situation in India in relation to the COVID-19 epidemic. Amit Agarwal has shared a solution using Google Apps Script which will let you receive an email notification of current vaccination slots in your city or zip area.

Source: Get Email Alerts When COVID-19 Vaccines Become Available Near You – Digital Inspiration

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

Generating custom travel advice links for Nederlandse Spoorwegen using Google Apps Script

I would like to send clients a travel advice (url) but I don’t know how to do that with the Maps Service. Luckely the website of the ‘Nederlandse Spoorwegen’ offers a possibility to do what I want to do (with some Google Apps Script).

For our fans in the Netherlands a nice little snippet from Mariette Timmer that builds a custom url to use with the Nederlandse Spoorwegen travel advice site. To solve this Mariette uses the Google Maps geocoder to convert an address into lat/long for needed for Nederlandse Spoorwegen.

Source: Travel advice #GoogleAppsScript

Adding Slide Page Link to Shape using Google Apps Script

This is a sample script for adding the slide page link to the shape using Google Apps Script

Nice little snippet that might be useful to look at just for an example for adding shapes to Google Slides.

Source: Adding Slide Page Link to Shape using Google Apps Script

How to Print the Function Call Flow with Stack Trace in JavaScript/Google Apps Script – Digital Inspiration

The printStackTrace method of Java is useful for handling exceptions and errors during development. It tells you the exact line number in your source code and the file name where the problem occurred.

If you are working in the JavaScript / Google Apps Script world, you can use the console.trace() method to output the complete stack inside the web console ( or StackDriver logs for Google Scripts).

A better alternative is that you parse the stack property of the Error object. This contains the entire stack trace along with line numbers, column position and the function names.

Great debugging tip from Amit that you can use in Google Apps Script for parsing the entire stack trace along with line numbers, column position and the function names. Check the source link below for example code and usage.

Source: How to Print the Function Call Flow with Stack Trace in JavaScript – Digital Inspiration

Subscribe to Apps Script Pulse...