AppsScriptPulse

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

A new approach for limiting access to a part of a Google Apps Script Web App

I’ve noticed that several Google Apps Script users are looking for a secure way to restrict access to their Web App. In my case I am looking for a way to let everyone have access to some functions of the web app, while a control panel must be reserved for the administrator (me).

I wanted to share my approach that makes use of the novelty introduced in GAS on March 15, 2021.

Interesting thread in the Google Apps Script Google Group community discussing approaches to creating Web Apps with admin interfaces. Follow the source link for more information and the full discussion.

Source: A new approach for limiting access to a part of a Google Apps Script Web App – Google Groups

Google Apps Script Example: Automated YouTube Stats Workflow

Totally Unscripted: Hello Alternate Runtimes – How to build Google Workspace Add-ons without using Apps Script – Thur 22 April at at 1200 PDT / 1500 EDT / 2000 GMT+1

Last month Google announced the general availability of alternate runtimes for Google Workspace Add-ons. With alternative runtimes, developers can write Add-ons in any language and on any infrastructure, and then publish them to the Google Workspace Marketplace. In this episode we are joined by Riël Notermans, owner of Zzapps (and Google Developer Expert) and Steven Bazyl (Developer Relations, Google Cloud). Riël, whose teams have been developing on Google Workspace for over a decade, will share his team’s key takeaways on Alternate Runtimes and Steven will provide additional tips on getting started including the GWAO Card Builder.

Tune in live to ask questions via the YouTube chat https://youtu.be/zDZqENU4B6s

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

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

Totally Unscripted: Google Apps Script – Discover, Learn and Share Thur 15 April at at 1200 PDT / 1500 EDT / 2000 GMT+1

In this episode we are going to delve into the topic of how you discover, learn and can share Google Apps Script projects, tips and tricks. We hope we can give you some useful advice if you, friends and colleagues are just starting out coding with Apps Script, or tips if you have been coding for a while and want to improve your Apps Script knowledge. For this episode we’ve got two great guest Chanel Greco – saperis (Founder & CEO) and regular Apps Script contributor Bruce Mcpherson, who’s recent work on the visual Google Apps Script explorer tool, scrviz, is providing developers with new ways to explore and replicate existing Apps Script projects.

Tune in live to contribute to the YouTube live chat (recording immediately available). – https://youtu.be/pvwwr2dLkdA

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

Github as an Apps Script cache platform – Desktop Liberation

Use Github as a back end for caching large objects across platforms. It’s possible to use a github repo as the back end for a caching service, and by taking this approach we can share data between Apps script and multiple platforms. It also means you can retrieve the data with the git CLI in addition. It works in exactly the same way as all the other backends. … Using a regular git Repo means you can make it private and share it using the tools already built into Github.

Source: Github as an Apps Script cache platform – Desktop Liberation

Plotting Points on Image using Google Apps Script · tanaike

This is a sample script for plotting the points on an image using Google Apps Script. Unfortunately, in the current stage, there are no methods for directly editing the image and putting the texts and some shapes in the methods for Google Apps Script. So in this case, it is required to use the workaround.

Clever workaround that uses Google Slides as a canvas for adding text to images.

Source: Plotting Points on Image using Google Apps Script · tanaike

Subscribe to Apps Script Pulse...