AppsScriptPulse

Solutions, Forms, Sheets, Beginner

A Google Workspace Parents Evening Appointment System — by Baz Roberts

Tags:
| | 0 Comments |

This is the first of a three-part post, where we’re going to look at how you can create an appointment system using Google Forms and Sheets and with the use of Apps Script, how you it will update the available times on the forms and how it will send automate confirmation emails to those making the appointments. I’ve used this system for parents evening meetings, but it could be adapted for any area that need appointments.

This series of posts from Baz Roberts details an appointment booking system created in Google Sheets and Forms. There is a lot of details, tricks and tips across the post series so well worth spending the time to unpick what has been shared.

Source: Parents Evening Appointment System [Alternative link: Parents Evening Appointment System (bazroberts.com)]

Add-on, Solutions, Sheets, V8, Advanced

Google Workspace Editor Add-on for encrypting/decrypting columns in Google Sheets – Desktop Liberation

Tags:
| | 0 Comments |

In ‘Merging sheets from multiple sources and encrypting selected columns’ I published some code for selectively copying columns from multiple input spreadsheets/sheets to create summary sheets, and optionally encrypting columns. The idea was to distribute the same sheet to multiple people, along with private key(s) to decrypt columns to which they should have access. This seems a pretty handy thing to make into an Editor Add-on. I’m too impatient and life’s too short to bother getting into the Add-on publishing process, so I haven’t officially released this one personally, but here is a fully functional decryption Add-on that any of you are welcome to fiddle with and publish yourself.

Having previously featured some of Bruce Mcphersons previous posts on encrypting/decrypting Google Sheets data it seems fitting to share the post containing the final add-on. Even if you are not interested in the add-on’s functionality this post can still be worth a look to see how Bruce structures an add-on and uses Vue and Vuex to create the UI rendered with HTMLService.

Source: Add-on for decrypting columns in Google Sheets – Desktop Liberation

V8, Intermediate, Sheets, Slides

How to Create Dynamic Open Graph Images with Google Sheets [and Google Apps Script] – Digital Inspiration

Tags:
| | 0 Comments |

Generate dynamic Open Graph images for your website with Google Sheets without requiring Puppeteer. All pages on your website can have their own unique Open Graph images created from a Google Slides template.

As explained by Amit Agarwal Open Graph images are included as a feature image on social media sites when the link is shared. You can use static images but some sites like Github dynamically create an image that includes additional information.

For example, if you were to share the link to the Google Workspace Solutions Github repo on Twitter an image is automictically displayed containing information like the number of contributors, issues, stars and forks:

Google Solutions - Open Graph from Github

To generate these images often a headless browser solution like Puppeteer is used. In this example from Amit he shows how a Google Slides template and a little Google Apps Script can be used to batch create similar open graph images.

Source: How to Create Dynamic Open Graph Images with Google Sheets – Digital Inspiration

Solutions, Sheets, Beginner

Repeatable Task [in Google Sheets]: To Script Or Not To Script, That Is The Question

Tags:
| | 0 Comments |

Image credit: benlcollins.com

A framework for thinking about whether you should automate a repeatable task in Google Sheets with scripts or not

Ben Collins provides some great advice on when you should think about automating tasks in Google Sheets. Click through the source link at the bottom for the full post for some great tips.

A graphic that stood out for me was the XKCD comic strip below.


… the math didn’t quite add up in my head so I double checked

Based on CC-BY-NC https://xkcd.com/1205/

Source: Repeatable Task: To Script Or Not To Script, That Is The Question

Official News, Beginner, Sheets

Learn how to improve Google Sheets performance

Tags:
| | 0 Comments |

Image credit: Google

You can improve the performance of Google Sheets and speed up calculations.

Via Sourabh Choraria (@schoraria911) we picked up this tweet from
Michael Avrukin who is working on the Google Sheets help center documentation:

The page includes a number of useful tips to speed up Google Sheet calculations and whilst not specifically Google Apps Script related provides useful information on some things you can look out for when your Google Sheet starts getting slow.

Source: Learn how to improve Sheets performance

Solutions, Sheets, V8, Intermediate

Bulk reset slicers in a Google Sheets, the Apps Script way

Tags:
| | 0 Comments |

This article explains how the filtering criteria of Google Sheets slicers can be reset using a few lines of Apps Script code. Two different approaches are shown. The first one uses the Spreadsheet Service, while the second one draws upon the Google Sheets API using the Advanced Sheets Service. Additionally, some shortcomings and peculiarities of both methods are highlighted.

Kudos to Pablo Felip for taking the time to explore and write-up this post about Google Sheets ‘slicers’. It contains some very useful tips and advice about  interacting with them using Google Apps Script.

Source: Bulk reset slicers in a Google Spreadsheet, the Apps Script way

Solutions, Sheets, Web App, V8, Intermediate

Google Apps Script library for parsing HTML form objects and adding the values to a Google Sheet

Tags:
| | 0 Comments |

This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.

A common Google Apps Script use case is taking data from a webform and adding it to a Google Sheet. This can sometimes be quite painful has you have to handle the various input types and also write the data to the correct columns. The HtmlFormApp library makes this very straight forward and to illustrate once the library is added to your Apps Script project you can start appending data in a couple of lines of code:

// These are all options.
const obj = {
   formData: formData,
   spreadsheetId: "###",
   sheetName: "###",
   sheetId: "###",
   folderId: "###",
   headerConversion: {"header value of Spreadsheet": "name of HTML input tag",,,},
   ignoreHeader: true,
   choiceFormat: true,
   delimiterOfMultipleAnswers: "\n",
   valueAsRaw: true
};
const res = HtmlFormApp.appendFormData(obj);
console.log(res)

Click through to the source link for more details 👇

Source: GitHub – tanaikech/HtmlFormApp: This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.

Solutions, Sheets, V8, Intermediate

How to Request Stripe Payments with Google Sheets – Digital Inspiration

Tags:
| | 1 Comment |

Image credit: Amit Agarwal

How to use Google Sheets to generate Stripe payment links and request payments in any currency from your customers anywhere in the world!

Amit Agarwal continues an excellent series of payment themed posts with this latest contribution on integrating the new Stripe Payment Links API with Google Sheets. As noted by Amit this can be combined to make quick payment workflows. Click through for more details and the source code.

Source: How to Request Stripe Payments with Google Sheets – Digital Inspiration

Solutions, Sheets, V8, Intermediate

Importing CSV data by keeping number formats of cells on Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for importing a CSV data by keeping the number formats of cells on Google Spreadsheet using Google Apps Script.

When a CSV data is manually put using the default UI using the browser, it seems that the number formats of all cells cannot be kept. In order to import the CSV data to the cells with keeping the number formats, it is required to use a script as a workaround. In this workaround, Google Apps Script is used.

When the following sample script of Google Apps Script is run, a dialog is opened. And, when the CSV file is selected from your local PC on the dialog, the CSV file is loaded, and then, the CSV data is put to the active cell with keeping the number formats of cells.

The quoted post description explains everything you need about this solution and worth clicking through to see how the code works.

Source: Importing CSV Data by Keeping Number Formats of Cells on Google Spreadsheet using Google Apps Script

Solutions, Sheets, Beginner

Archive Sheet data using tickboxes and Google Apps Script

Tags:
| |

Automatically move a row of data from one Google Sheet tab to another when a tickbox is selected.

Archive a row of data by ticking a box

Archive a row of data by ticking a box

The following Google Apps Script is designed to move a row of data from one Google Sheet tab to another when a tickbox has been selected in a given column. The tool was developed to help colleagues with automatically archiving data that had been actioned.

The tool uses an ‘onEdit(e) simple trigger’ to constantly monitor the Google Sheet for any changes in values. Is it specifically targeted at a tickbox-column only so that it does not archive rows of data on every edit of the Google Sheet.

Source: The Gift of Script: Archive Sheet data using tickboxes