AppsScriptPulse

Machine learning in Google Sheet with Tensorflow.js and Google Apps Script 

This article will show you how you can setup, train, and predict spreadsheet data with deep-learning framework Tensorflow.js. You don’t need to call REST APIs or use other 3rd parties storage and algorithm. All your data stays in your secure Google Sheet.

Source: kutil.org: Machine learning in Google Sheet with Tensorflow.js and Google Apps Script

Moving Card Game using Google Sheets & Apps Script

Moving Card Game using Google Sheets & Apps Script

One popular method to get your students to learn is to categorise things or to put them in order. Here, we’re going to look at making a card sorting game, which could be used for both.

Source: Moving Card Game using Google Sheets & Apps Script

Lessons Learned building an Apps Script Add-on | Two Octobers

After joining Two Octobers this March, I was overjoyed to be able to focus on building tools that we can use to help grow client revenues. One tool that I had percolating in the back of my mind was a Google-Sheets-based Add-on for building and managing Google My Business Posts. Our team, codenamed “Skunkworks”, got to work and built our Add-On, AgencyAutomators – POSTS. While in the moment it felt like one small step for each of us, it felt like a huge leap for Two Octobers. We learned a ton along the way that I’ll be sharing with you here.

A very comprehensive guide on the challenges and solutions for developing Google Apps Script projects in teams. The post has lots of tips and tricks Noah and his team at Two Octobers learned along the way including hurdles overcome as part of the Add-on publication process. Follow the link for more details.

Source: Lessons Learned building an Apps Script Add-on | Two Octobers

Create Google Doc from Form submission

Screenshot of spreadsheet with data and Doc link

Screenshot of spreadsheet with data and Doc link

Create a Google Doc for each submission of a Google Form and translate some of that Form data into the Doc. It puts a link to the newly created Google Doc back into the spreadsheet alongside the relevant row. The Name field from the Form is used as part of the filename for the created Doc and there is a Log sheet to support any troubleshooting errors.

Source: The Gift of Script: Create Google Doc from Form submission

Build Local Links with Google Apps Scripting

Building up a solid link profile is a cornerstone of local SEO, but it can be a time consuming and tedious process. In this post I’ll show you how to automate this quickly and efficiently with Google Sheets.

This Google Sheets tool uses Apps Scripting to combine the Google Maps and Mozscape APIs into an all-in-one link finder. Type in a search term and a lat/long, and the tool will find the top Google maps results. Once we have those results, we can discover who is linking to them, and produce a “link intersect” report.

Source: Build Local Links with Google Apps Scripting

Dear Analyst #31: Writing Google Apps Scripts to sync data from Coda to Google Sheets • KeyCuts Blog

Use cases for Google Apps Script include syncing data from Coda to Google Sheets.

Always nice to see other products promoting integrations to Google with Google Apps Script. In this post/podcast Al Chen links to a detailed tutorial and code for syncing data to and from Google Sheets to Coda. If you aren’t familiar:

Coda brings all of your words and data into one flexible surface. Coda comes with building blocks—like tables and buttons—and time-saving templates, so your doc can grow and evolve with the needs of your team.

Follow the link through to the source for code and more details.

Source: Dear Analyst #31: Writing Google Apps Scripts to sync data from Coda to Google Sheets • KeyCuts Blog

Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script

Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script – submit.md

An example script from Kanshi TANAIKE which lets you test the new onSelectionChange(e) simple event trigger in Google Sheets.

The onSelectionChange(e) trigger runs automatically when a user changes the selection in a spreadsheet.

In the example shared this is used to detect the user changing Google Sheet tab.

Source: Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script

Interacting with multiple hyperlinks in Google Sheets cells with Google Apps Script

Before this, when a cell has only one hyperlink. In this case, the hyperlink was given to a cell using =HYPERLINK(“http://www.google.com/”, “Google”) but by a recent update, a cell got to be able to have multiple hyperlinks … In this report, I would like to introduce the method for setting and retrieving the multiple URLs for a cell.

Hyperlinks in Google Sheets cells is a bit of an obsession of mine and it’s nice to see Kanshi TANAIKE has a similar passion. Google are rolling out multiple hyperlinks in Google Sheet cells and Tanaike has provided details on how the hyperlink values can been get/set with Google Apps Script (the official docs are still catching up with Tanaike’s discovery :)

Source: Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell

Google Apps Script: Upload grades into a Google Classroom Coursework Assignment – Yagisanatode

Google Classroom has become one of the darlings of the teaching world that has come out of this crisis. Its shallow learning curve and attractive UI made it an obvious choice for most educators. Oh, and I am sure the fact that it is free helped too.
One of the limitations is that you cannot simply upload grades from, say Google Sheets or another assessment tool. Well…not directly in the Google Classroom platform, yet.

This tutorial runs through the basics of uploading grades from Google Sheets into Google Classroom using Google Apps Script with the Classroom API.

Source: Google Apps Script: Upload grades into a Google Classroom Coursework Assignment – Yagisanatode

DNSQuery and G Suite domain detection in Google Sheets using the Cloudflare name resolution service

Dos funciones personalizadas para hojas de cálculo de Google desarrolladas en Apps Script que proporcionan un envoltorio para la función NSLookup, tal y como aparece en la documentación del servicio de resolución de nombres de CloudFlare. Consulta el registro indicado en el o los dominios que se pasan como parámetro utilizando el servicio de resolución de nombres de CloudFlare. Determina si un email o dominio (o lista de emails o dominios) está gestionado por Google o no. – pfelipm/dnsquery

Pablo Felip Monferrer has shared two custom functions for Google Sheets that provide an extension to the NSLookup function originally shared by Cloudflare. The first is a wrapper that makes it easy to list specific DNS record types for a single or list of domains. The second function uses the domains MX record to automatically detect if a domain has G Suite Gmail settings applied.

Source: pfelipm/dnsquery

Marketing Data Extraction using SOAP API — Apps Script Automation

Recently I had setup a daily extraction automation from a marketing platform and they had SOAP API. I had worked with xml before, so after some trial and error I was able to fetch marketing data from trafficvance.com platform right inside Google Sheets

Nice post from Urwa Shabir highlighting how to interact with 3rd party APIs using SOAP which relies heavily on XML. The post covers formatting a SOAP request now that SOAPService is deprecated in Apps Script, as well as parsing the XML response.

Source: Marketing Data Extraction using SOAP API — Apps Script Automation

DNS in Google Sheets – Cloudflare Resolver

They thought we wouldn’t go there, but they thought wrong. If you want or need to find out some DNS records inside of Google Sheets, create a Google Function

Eric Koleda spotted (see tweet) that Cloudflare has been having some fun with Google Apps Script sharing a custom function that lets you do a DNS lookup for domain names in Google Sheets. Eric notes he can’t imagine when you would need to do this, but I always find it encouraging to see big companies using and promoting Google Apps Script.

Source: DNS in Google Sheets – Cloudflare Resolver

Hacking it: Generate PDFs from Google Forms

Some time ago, I had a request from one of my colleagues to help automate a rather repetitive and might I add, highly prone to error, process. One of the clients required her to fill in the same form several times in a day, manually, and then send it back for approval and signing. Of course, I’m simplifying a bit but that was what it was in essence.

In this detailed post, Neha demonstrates how to use Google Forms to generate professional looking PDFs from the data submitted via the form, using Google Sheets and Apps Script.

Source: Hacking it: Generate PDFs from Google Forms

Streamline access to resources for vendors via Google Sheet – Sheets to Apps

Need to share a variety of documents with vendors? Don’t want to share access one by one? In this episode of Sheets to Apps, we’ll cover how to use an Apps Script in a Google Sheet to automatically add members to a Google Group, helping you share documents at a large scale that will save you time.

We’ve previously shared the Medium post that provides more details for setting up this solution. If you missed that post or were unclear Alexandrina Garcia-Verdin, better known in the community as AGV, provides a very useful overview.

The code and other resources presented in this episode are available from the video description.

How to format cells through code in Google Sheets

If you had used the GUI to manage your custom formulas used for conditional formatting, then to see and manage that logic, you would have to find and select the correct cell, or select the entire spreadsheet, and then right click and select “Conditional Formatting”, or go to the menu and select “Format -> Conditional Formatting”, to actually see and edit that logic. Which can be quite inconvenient at times.

Set conditional formatting rules through your script, which would allow you to keep everything configured in a single script.

Source: How to format cells through code in Google Sheets

Grant access to resources 🚣 from a Google Sheet in G Suite

Use a Google Group to work cross-functionally with vendors, partners, customers, and volunteers outside of your domain, and scale access to work assignments and leverage security controls.

Share documents, events, and communications with users outside (or inside) your G Suite domain.

Source: Grant access to resources 🚣 from a Google Sheet in G Suite

Capture Gmail Messages in a Google Sheet using Google Apps Script | Practical Ecommerce

With Google Apps Script, marketers can capture Gmail messages, parse them for relevant information, and add them row-by-row to a Google spreadsheet. This can be especially helpful for capturing leads or contacts.

A comprehensive write-up from Armando Roggio on using Google Apps Script to copy emails from your Gmail account to Google Sheets. As an added bonus the tutorial is also written using the new V8 runtime syntax.

Source: Capture Gmail Messages in a Google Sheet | Practical Ecommerce

A bulk email/mail merge with Gmail and Google Sheets solution evolution using V8 – MASHe

Last year I had a ‘mail merge using Gmail and Google Sheets’ contribution accepted on the G Suite Developers Solution Gallery. Just over 6 months on there has been lots of useful feedback and requests for solutions to some of the limitations of the original published script. In the meantime Google has also made the new V8 runtime for Google Apps Script generally available. Given this it seemed appropriate to revisit the original solution updating it for V8 as well as covering the most commonly asked for changes. In this post I’ll highlight some of the coding changes as well as some easy modifications.

This post picks up some common requests I get for features like advanced send parameters (cc, bcc, sender name/from, replyTo), sending emails with emoji/unicode and scheduling/triggering bulk emails. Some other areas covered in this post might be of general interest to Apps Script developers interested in using formatted Google Sheets cell values for currencies, dates and more as well as detecting/ignoring filtered hidden rows.

Source: A bulk email/mail merge with Gmail and Google Sheets solution evolution using V8 – MASHe

How To Connect Google Sheets To A Database, Using Apps Script

App Script comes with a JDBC Service that lets you connect to MySQL, Microsoft SQL Server, and Oracle databases.

This is a guest post from Mike Ritchie (on benlcollins.com), co-founder of Seekwell.io, which adds SQL to the apps you need it in.

Source: How To Connect Google Sheets To A Database, Using Apps Script

Everything a Google Apps Script Developer wanted to know about reading hyperlinks in Google Sheets … but was afraid to ask

The problem: hyperlinks in Google Sheets can be in three different formats so if you have a script or add-on that relies on handling links in cells it can cause some headaches.

This article covers a thorough walkthrough of using Advanced Sheets Service to read hyperlinks from cells with different link formats.

Source: Everything a Google Apps Script Developer wanted to know about reading hyperlinks in Google Sheets … but was afraid to ask

Subscribe to Apps Script Pulse...