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

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

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

How to Download Speaker Notes in Google Slides – Digital Inspiration

With Creator Studio, you can easily convert your Google Slides presentation into animated GIFs and video slideshows. The add-on can also extract speaker notes from your slides and export them as a text file in Google Drive. Internally, the app uses Google Apps Script to export Speaker Notes from your Google presentation and writes them to a text file inside Google Drive.

In this post Amit Agarwal share some very clean code for iterating across each slide in Google Slides and export the speaker notes into a single file. Click through to the post for the source code.

Source: How to Download Speaker Notes in Google Slides – Digital Inspiration

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

Firefast is a Firebase SDK for Apps Script V8 Runtime. Read and write data in Firebase using Google Apps Script

Firebase Realtime Database is a cloud-hosted NoSQL database. Data is stored as JSON and can be accessed in your Web, iOS, Android app using Google’s Firebase SDK. But, Google doesn’t provide such SDK for Apps Script. This library solves that problem. It gives you Firebase SDK for Apps Script.

Nice Google Apps Script library from Mani Doraisamy that makes it easy to read/write data from a Firebase Realtime Database. The site also highlights the performance gain of writing this library from the V8 runtime compared to the older FirebaseApp library shared by Romain Vialard. Mani’s code is open source and if you have issues, feedback or contributions you can add these via the Github repo.

Source: Firefast – Getting Started

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

Apps Script V8: Keystore for global space – Desktop liberation

One of the challenges with V8 compared to Rhino is that you can’t be sure of the order of global variable initialization. Personally this is not an issue for me, as I avoid any such use of global space, but it is an issue for some. A nice way of dealing with global space is to use namespaces and IEF as I described in Apps Script V8: Multiple script files, classes and namespaces but another, perhaps less obvious way, is put all the variables (and even functions) you need to access from multiple functions in a keystore.

Interesting solution for storing/retrieving variables, particularly when you are using the new V8 runtime.

Source: Apps Script V8: Keystore for global space – Desktop liberation

Apps Script V8 Runtime Explained For Non-Professional Developers

Learn how to use modern JavaScript features in your Apps Script code with the release of the Apps Script V8 runtime engine.

Somehow we missed this post from Ben Collins when it was originally published in February, 2020, but it is well worth visiting and adding to our V8 collection of posts. For those still catching up V8 is the new runtime for Google Apps Script which enables modern JavaScript syntax to be used in your code. Ben covers some of the basic differences now possible with V8, with lots of examples.

Source: Apps Script V8 Runtime Explained For Non-Professional Developers

Apps Script V8: Multiple script files and namespaces – Desktop liberation

Apps Script doesn’t have a module loader system. If you’re used to developing in NodeJs, you’ll also be familiar with importing and exporting to include required modules in your project. With Apps Script, you have no control over the order in which multiple script files are executed. In Legacy Apps Script, there seemed to be some kind of workaround going on so that global statements were executed in a sensible order (I don’t know the details), but in V8 this is not the case. …

My golden rules are

  • Nothing executable should be in global space
  • Don’t rely on the order that things are processed
  • Minimize the number of executable functions (1 is good)
  • Always assume your code will be reused somewhere else.

Source: Apps Script V8: Multiple script files and namespaces – Desktop liberation

Apps Script V8: spreading and destructuring – Desktop liberation

V8 adds destructuring from  JavaScript ES6. Legacy Apps Script already had destructuring of arrays added fairly recently, but v8 gives full a destructuring capabilitity. These destructuring and spreading capabilities, which at first may again seem a little like syntactic sugar, have contributed greatly to  the development of state management frameworks such as Vuex and Redux for client side apps. V8 brings some of that cleanliness to Apps Script.

Source: Apps Script V8: spreading and destructuring – Desktop liberation

JavaScript V8 Arrow functions, this and that – Desktop liberation

V8 adds the arrow function declarator from modern JavaScript. This is a handy shorthand but it’s more than just that. There are some behavioral differences too that you’ll need to understand before diving in. The old way of declaring functions still exists of course, and there will always be a need for it.

More tips and guidance from Bruce Mcpherson for developers migrating code to the Apps Script V8 runtime.

Source: JavaScript V8 Arrow functions, this and that – Desktop liberation

Apps Script V8: Maps and Sets – Desktop liberation

Sets and Maps can often be a cleaner way of storing data than using Objects or Arrays, even though at first glance they may seem a little redundant. Unlike an array, they are aware of what else is in the map or set (so you can avoid duplicates), and unlike an object, you can use anything as the key – including the item value itself

Given many Apps Script projects focus on manipulating data Bruce Mcpherson provides a useful introduction to Maps and Sets.

Source: Apps Script V8: Maps and Sets – Desktop liberation

Apps Script V8: Template literals – Desktop liberation

It’s a shorthand way of using a template into which variables are subsituted in a string. This allows for better reuse of string structures, and a few other goodies besides (like all V8 additions, it’s more than just a syntactical spruce up)

Bruce Mcpherson has a very useful post highlighting the benefits of using the new V8 runtime when working with string output

Source: Apps Script V8: Template literals – Desktop liberation

Apps Script’s new V8 runtime | Google Cloud Blog

This update to Apps Script lets users take advantage of modern JavaScript features and enhances new or existing scripts with less verbose, more standardized code.

This post from Alexandrina Garcia-Verdin, better known in the community as AGV, highlights some of the benefits of the new Google Apps Script V8 runtime including succinct syntax and data processing operations: map(), filter() and reduce(). As part this AGV also highlights some of the resources from Ben Collins and Amit Agarwal put together to support the community transition to V8. A nice feature of this post from AGV is side-by-side comparisons of old and new JavaScript syntax:

Image source: Google

Source: Apps Script’s new V8 runtime | Google Cloud Blog

G Suite Solutions —  Apps Script Powered Workflow Automation

Laura Taylor recently spoke at SheetsCon on the topic of Automation with Apps Script. During the presentation, she walked through a simple approval workflow for a hypothetical small business to demonstrate the power of Apps Script to automate workflow inside G Suite. She also showcased modern ECMAScript features supported by the new Apps Script V8 Javascript runtime.

The article is an overview of the workflow covered in the author’s presentation along with install instructions for trying it out in your G Suite domain.

Source: G Suite Solutions —  Apps Script Powered Workflow Automation

Things one can get used to for the V8 Google Apps Scripts engine

A bit of a monologue about various syntax changes and adjustments in learning and using the tool.

Great post from Adam Morris (@clssrmtechtools) aimed at Google Apps Script developers wanting to start coding with the more modern JavaScript syntax used in V8. Adam shares lots of great advice to get started and build upon.

Source: Things one can get used to for the V8 Google Apps Scripts engine

Google Apps Script V8 variable scopes – var, const and let – Desktop liberation

One of the key things that V8 has sorted out is the scope of variables. Using var to declare variables meant that anything declared within the scope of a function could easily be accidentally overwritten, causing hard to track down errors. ES6 (since it’s commonly known as V8 in Apps Script – I’ll be referring to it as V8 from now on), has added const and let to the variable declaration vocabulary to help prevent these kind of problems.

With the introduction to the new Google Apps Script V8 runtime new opportunities are available for writing clean and robust code in the script editor using modern JavaScript syntax. In this post from Bruce Mcpherson you can learn some of the basics of var, const and let.

Source: JavaScript V8 variable scopes – Desktop liberation

Subscribe to Apps Script Pulse...