AppsScriptPulse

Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet – Yagisanatode

Have you ever asked for a list from a client, another department or agency and instead of saving into a file that might even be considered marginally useful, they give it to you as a pdf. … Continue reading “Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet”

Yagi shares a comprehensive walk-through of how you can sometimes extract data from .PDF documents using Google Apps Script and the advance Drive service. The solution is also able to work on .PDFs created from images using the built-in OCR features of Google Drive and you might want to drop the getTextFromPDF() function shared in the post.

Source: Google Apps Script: Extract Specific Data From a PDF and insert it into a Google Sheet – Yagisanatode

How macros can be used to build Apps Scripts for cleaning data – Sheets to Apps

Cleaning up data from downloaded accounting reports or from other systems can take up valuable time and resources. In this episode of Sheets to Apps, we’ll walk you through adding automation to your monthly Excel accounting data that records your steps into a script that you can run repeatedly with Google Sheets macro recorder.

From the video description you’ll find all the links you need to the example Google Sheet, instructions and more

Calculating moving averages in Google Sheets with Google Apps Script (and tips on writing custom functions)

Por esa razón me he puesto manos a la obra y he preparado MEDIAMOVIL(), una función personalizada GAS capaz de calcular varios tipos de medias móviles, que puede ser utilizada (como todas las funciones personalizadas) de manera combinada con el resto de funciones integradas que nos ofrecen las hojas de cálculo de Google.

TRANSLATED: For this reason I have gotten to work and have prepared MEDIAMOVIL(), a custom GAS function capable of calculating various types of moving averages, which can be used (like all custom functions) in combination with the rest of the integrated functions. offered by Google spreadsheets.

Great post from Pablo Felip (@pfelipm) on calculating moving averages in Google Sheets. As part of this Pablo has developed and shared the MEDIAMOVIL() custom function he has developed in Google Apps Script. Clicking through to the source code on Github Pablo has also shared lots of advice on developing custom functions for Google Sheets including using the contextual help features available in custom functions as well as handling parameters and throwing appropriate error messages to the user.

Source: Calculando medias móviles con Google Apps Script

Highlighting Row and Column of Selected Cell in Google Sheets using Google Apps Script · tanaike

This is a sample script for highlighting the row and column of the selected cell using Google Apps Script … and the OnSelectionChange event trigger

Nice little script solution which could be a very useful addition to your script projects as an accessibility feature.

Source: Highlighting Row and Column of Selected Cell using Google Apps Script · tanaike

RichTextApp – a Google Apps Script library for copying rich text formatting from Google Docs and Sheets

This is a GAS library for copying the rich text with the text styles from Google Document to Google Spreadsheet or from Google Spreadsheet to Google Document using Google Apps Script (GAS). And, also the rich texts in the cells can be converted to HTML format.

Nice little helper library for copying rich text formatting from Google Docs and Sheets. You can view the project README for information on the formats that are currently supported. Personally,  the inclusion of the RichTextToHTMLForSpreadsheet method looks particularly useful for converting rich text formatting in Google Sheets cells to HTML for mail merge applications.

Source: tanaikech/RichTextApp

Using Google Apps Script to disable custom functions assigned to Google Sheets buttons/images to prevent simultaneous execution · tanaike

This is a sample script for disabling the buttons put on Google Spreadsheet using Google Apps Script. When a script is run by clicking a button on Google Spreadsheet, there is the case that you don’t want to make users run the script in duplicate.

Nice little Google Apps Script snippet that prevents a function assigned to an image or drawing in Google Sheets from being run simultaneously. The clever bit is the code uses Google Apps Script to modify the function assigned to the button/image when it is running.

Source: Disabling Buttons Put on Google Spreadsheet using Google Apps Script · tanaike

Syncing Data from Coda to Google Sheets And Vice Versa with Google Apps Script [A How-To Guide] | Hacker Noon

Last year I published a tutorial on how to sync data between two Coda docs and data between two Google Sheets. What was missing from the tutorial was how to sync data between a Coda doc and a Google Sheet.

A step-by-step walk-through by Al Chen on syncing data from Coda -> Google Sheets and from Google Sheets -> Coda

Source: Syncing Data from Coda to Google Sheets And Vice Versa with Google Apps Script [A How-To Guide] | Hacker Noon

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

Subscribe to Apps Script Pulse...