AppsScriptPulse

Snippet, Solutions, Sheets, Beginner

Alexa Rank Tracker built in Google Sheets with formulas and Google Apps Script – bencollins.com

Tags:
| | 0 Comments |

Tutorial on how to build an Alexa Rank tracker in Google Sheets, using the powerful IMPORTDATA and REGEXEXTRACT functions and Apps Script.

Clever post from Ben Collins highlighting how Google Sheets functions can be used to scrape Alexa tracking data. As a bonus Ben includes a little snippet of Google Apps Script to let you archive data over time.

Source: Alexa Rank Tracker built in Google Sheets with formulas and Apps Script

Snippet, Beginner, Sheets

The Gift of Script: Search spreadsheet for multiple criteria

Tags:
| | 0 Comments |

Search through rows of a Google Sheet simultaneously looking for certain criteria all in one single loop. I needed a way of doing this without multiple loops despite searching for separate information and here is a way of achieving that via true/false flags.

Rows of email address and role in spreadsheet

Screenshot of spreadsheet data to search

Source: The Gift of Script: Search spreadsheet for multiple criteria

Snippet, Solutions, Drive, V8, Intermediate

How to Make your Documents Read-only in Google Drive using Google Apps Script – Digital Inspiration

Tags:
| | 0 Comments |

Your documents and files in Google Drive can be modified by anyone who has edit access to the file. You can always remove external collaborators from your documents to prevent them from editing your files but how do you prevent yourself (the owner) from accidentally editing your own files in Google Drive? Google Drive doesn’t have a simple button (yet) for locking files so here’s a little Google Script that can help you make any file in your Google Drive read-only.

Nice post from Amit Agarwal showing how the new Google Drive Locking API can be used with Google Apps Script. The post contains all the code and instructions you need to get started.

Source: How to Make your Documents Read-only in Google Drive – Digital Inspiration

Snippet, Solutions, Forms

Using Google Apps Script to generate code for handling Google Forms submission data

Tags:
| |

Automate the generation of further Apps Script code typically used when capturing Google Form submission data. I tend to find there are 2 lines of code per Form question that I have to write which when scaled up to 60+ questions is a lot of time and chance for human error.

Apps Script code can be automatically generate as lines of text in a document.

Auto generate Apps Script code lines.

Source: Generate Apps Script code for Forms

Snippet, Solutions, Sheets, V8

Adding some AutoFill magic to your Google Sheets projects with Google Apps Script

Tags:
| | 0 Comments |

Google Sheets users can already use the magic of AutoFill to expand data automatically detecting a series of numbers, letters or dates. This feature is particularly powerful even when your series includes text or repeating dates. In this post I’ll highlight how you can also use AutoFill in your Google Apps Script projects

This post is designed for both no/low coders and expert developers interested in finding out how you can use AutoFill in your Google Sheets/Apps Script projects.

Source: Adding some AutoFill magic to your Google Sheets Apps Script projects

Snippet, Solutions, Docs

Google Doc to clean HTML converter for Google Apps Script

Tags:
| | 0 Comments |

Export Google Doc as clean html. Handy to make a WordPress post from Google Doc. – thejimbirch/GoogleDoc2Html

Following on from our previous post highlighting Amit Agarwal’s Send Rich Text HTML Emails with Google Sheet  we received the following recommendation for a Google Doc to HTML converter solution from @IMTheNachoMan:

For those interested in workflows for using Google Docs as an email template this solution already has a emailHtml() function you can build on which appears to already inline images from your Google Doc.

Source: thejimbirch/GoogleDoc2Html

Snippet, Solutions, Sheets, V8

Send Rich Text HTML Emails with Google Sheet – Digital Inspiration

Tags:
| | 0 Comments |

You can format the source data in Google Spreadsheet in different colors, fonts and sizes and all your cell formatting will be retained in the email messages.

Very handy code snippet from Amit Agarwal which lets you convert the formatted text in a Google Sheets cell to HTML. In the example Amit uses the formatted text in an email sent with MailApp but you might find other uses for this solution. Click through to the source link for all the details…

Source: How to Preserve Formatting of Spreadsheet Cells in Mail Merge – Digital Inspiration

Snippet, Solutions, Slides, V8

Retrieving Data from QR code on Google Slides using Google Apps Script · tanaike

Tags:
| | 0 Comments |

This is a sample script for decoding a QR code put in Google Slides using Google Apps Script. In this case, Javascript is used at the opened dialog. And Canvas API and jsQR are used.

This is a nice example of how you can easily drop in existing JavaScript libraries into your Apps Script project by using a HTML dialog. The post also has some nice tricks for handing image data, in particular, from getDataFromQRCode() the image data is passed into the HtmlService:

const html = HtmlService.createTemplateFromFile("index");
html.image = JSON.stringify(blob.getBytes()); // adding image data
SlidesApp.getUi().showModalDialog(html.evaluate(), "sample");

This is added in client side with a printing scriptlet:

image.src = `data:image/png;base64,${btoa(String.fromCharCode(...new Uint8Array(Int8Array.of(...JSON.parse(<?= image ?>)).buffer)))}`; 

Another way you could achieve this is base64 encoding the image server side with Utilities.base64Encode().

Source: Retrieving Data from QR code on Google Slides using Google Apps Script · tanaike

Snippet, Solutions, Slides, V8

Cropping Images in Google Slides using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for cropping images in the Google Slides using Google Apps Script.

Useful snippet from Tanaike which shows how you can crop images in Google Slides by using the replace(blobSource, crop) method. The  post also highlights a limitation with the image manipulation methods available around crop centering.

Source: Cropping Images in Google Slides using Google Apps Script · tanaike

Snippet, Solutions, Sheets

Bulk export Qualtrics survey results to Google Sheets using Google Apps Script

Tags:
| |

The following tool is designed to connect to your Qualtrics account and export the results data for multiple surveys that you specify, in one go. The results will be appended to the Google Sheet using the survey-name to identify each sheet of data. The data itself is exported as CSV.

The spreadsheet requests some details to connect to your qualtrics accounts with clear instructions on how.

Screenshot of instructions from Google Sheet with boxes to enter information.

 

Source: The Gift of Script: Bulk export Qualtrics survey results

Snippet, Solutions, Forms

Control responses in Google Forms in 4 lines of Google Apps Script

Tags:
| | 0 Comments |

If you are using Google Forms to handle sign-ups for an upcoming event, and you want to control the number of responses (Limit responses), guess what! you can do it using Google apps script (only in 4 Lines of Code)

Nice little snippet highlighted by Aya Sayed, click through to the source for the code snippet.

Source: Control responses in Google Forms! | by Aya Sayed | Jul, 2020 | Medium

Snippet, Drive

Converting SVG Format to PNG Format using Google Apps Script · tanaike

Tags:
| | 0 Comments |

This is a sample script for converting the SVG image data to PNG image data using Google Apps Script. Unfortunately, in the current stage, there are no methods for directly converting the SVG to PNG in Google Drive service. But it can be achieved by Drive API

Nice trick to get .svg files stored in Google Drive in a raster format.

Source: Converting SVG Format to PNG Format using Google Apps Script · tanaike

Snippet, Solutions, Docs, Drive

The Gift of Script: Bulk create Google Docs from Google Sheet data

Tags:
| |

The following is designed to bulk create Google Docs from rows of data within a Google Sheet and to include some of that data within the new Doc body (so they become customised files). It also creates a link to the new Google Doc back in the Sheet on the relevant row.

Rows of data in a spreadsheet will transfer into a Doc

Example rows of data in a Google Sheet

Source: The Gift of Script: Bulk create Google Docs from Google Sheet data

Snippet, Solutions, Drive

Bulk create Google Drive folders 2.0 with Google Apps Script

Tags:
| | 0 Comments |

The following was developed to bulk create Google Drive folders with data from a given spreadsheet (eg to name the folders and add permissions). It is an improved version of the tool built in April 2019

Source: The Gift of Script: Bulk create Google Drive folders 2.0

Snippet, Solutions

How to Create Zoom Meetings with Google Apps Script – Digital Inspiration

Tags:
| | 0 Comments |

How to use the Zoom API to automatically create and schedule Zoom meetings with Google Apps Script

Great little snippet from Amit Agarwal showing how you can interact with the Zoom API using Google Apps Script. This post is also a great example if you are also looking for a code pattern for interacting with APIs using JSON Web Token (JWT). As Amit mentions it’s worth noting that:

We can build Zoom apps with the OAuth2 library as well but since this app is only for internal use and will not be publish to the Zoom marketplace, the JWT approach is easier.

Source: How to Create Zoom Meetings with Google Script – Digital Inspiration

Subscribe to Apps Script Pulse...