AppsScriptPulse

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

Snippet

Retrieve previous versions of Google Apps Script projects

Tags:
| | 0 Comments |

Photo by James Lee on Unsplash

Retrieve the source code linked to a specific version of your Apps Script project then save it as a new Apps Script project – RomainVialard/access-code-of-older-versions

Neat solution from Romain Vialard to retrieve a previous version of your Apps Script code. There is an open feature request to have this as a built-in feature of Apps Script (as noted by Romain recovering a previous script version is also possible using clasp using pull and an optional version number).

Source: RomainVialard/access-code-of-older-versions

Snippet, V8, Sheets

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

Tags:
| | 0 Comments |

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

Snippet, Solutions, Sheets, V8

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

Tags:
| | 0 Comments |

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

Comment, Snippet

Structure and simplify your Google Apps Script (with) V8

Tags:
| | 0 Comments |

Apps Script now supports the V8 runtime. Time for an update to the story “Structure and simplify your Google Apps Script Apps script”. The V8 runtime brings modern ECMAScript syntax to Apps Script. So we can now use arrow functions and destructuring assignments. No more hoisting with let and const and my personal favorite, template literals.

In this story Jasper Duizendstra shows the added value of the changes made in the constructor pattern using the factory functions. It also shows how to implement chaining and a builder pattern in Apps Script.

Source: Structure and simplify your Google Apps Script (with) V8

Snippet, Forms

Google Form – use a switch to determine save location

Tags:
| |

The following code is designed to take a specific option selected on a Google Form and use that to determine where something could be stored. The example it was developed for was Risk Assessments where depending on the type of assessment selected on the Form the generated file was to be saved in a specific Google Drive folder.

Source: The Gift of Script: Google Form – use a switch to determine save location

Snippet, Sheets

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

Tags:
| | 0 Comments |

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

Snippet, Solutions, Sheets

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

Tags:
| | 0 Comments |

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

Snippet, Drive

The Gift of Script: Create a Shortcut in Google Drive with Google Apps Script

Tags:
| | 0 Comments |

The following Google Apps Script is designed to create a Shortcut to an existing Google Drive folder, within another folder..

For Google Drive pros you can currently place a file in multiple folders, but Google will be removing this functionality on 30 September 2020. In March Google announced general availability of shortcuts in Google Drive, is is designed to replace the ‘file in multiple locations’ feature. For Google Apps Script developers wanting to migrate to Drive Shortcuts Phil Bainbridge provides this useful Apps Script snippet.

Source: The Gift of Script: Create a Shortcut in Google Drive

Snippet, V8

Apps Script V8: Keystore for global space – Desktop liberation

Tags:
| | 0 Comments |

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

Snippet, Gmail

Using Google Apps Script to capture who sends email from your delegated gmail account

Tags:
| | 0 Comments |

Subscribe to Apps Script Pulse...