AppsScriptPulse

Add-on, Solutions, Docs, Forms, Sheets, Slides, Intermediate

How to easily add the same Custom Menu to Docs, Sheets, Slides and Forms with Google Apps Script

Tags:
| | 0 Comments |

Use Google Apps Script to create a custom menu that will work inside Google Sheets, Google Docs, Slides and Google Forms.

Tired of writing separate code for custom menus in Docs, Sheets, Slides, and Forms? This nifty Google Apps Script snippet by Amit Agarwal helps you build a universal menu that works across all these apps!

The solution is a small snippet which detects the context to see which of DocumentApp, SpreadsheetApp, FormApp, SlidesApp are available which then lets you switch to get the appropriate .getUI() method.

Check out the source post for the full code and streamline your add-on development!

Source: How to Add a Universal Custom Menu to Multiple Google Workspace Apps – Digital Inspiration

Solutions, AppSheet, Forms, No Code

Google Forms automation with AppSheet, automatic emails and reminder with ease

Tags:
| | 0 Comments |

Power of Google Forms with Power of AppSheet make Forms automation super easy and efficient.

Following our TU Special on building AppSheet automations using the new Google Forms integration, Stéphane Giron shared a Medium post detailing a couple of example use cases. In the post, Stéphane shares some intermediate examples that go beyond the basics, with the potential for conditional routing and advanced automations.

If your automations are not being triggered by Google Form submissions, Pablo Felip’s tip is it could be because your Google Workspace Admin has turned on AppSheet Core Security.

Source: Google Forms automation with AppSheet, automatic emails and reminder with ease

Solutions, Drive, Forms, Intermediate

Move files uploaded with Google Forms to specific folders in Google Drive with Google Apps Script

Tags:
| | 0 Comments |

Learn how to move uploaded files from Google Forms to a specific folder in Google Drive. You can also rename the files based on the form responses

Amit Agarwal, the brains behind Digital Inspiration’s Document Studio must have one of the most feature full Google Workspace Marketplace Add-ons. I also like Amit’s approach of sharing how you can solve problems like moving Google Form file uploads using Document Studio as well as providing an Apps Script snippet if you would like to manually code it.

In this example some setup is required to specify a folder ID and create an onFormSubmit trigger. After this the script then retrieves uploaded files from the form response, creates a subfolder named after the unique Response ID, and moves the files into that subfolder within the specified parent folder. This provides a structured and organized way to manage file uploads from Google Forms.

Follow the source link to read more about this script solution and about Document Studio.

Source: How to Move Files Uploads from Google Forms to Specific Folders in Google Drive – Digital Inspiration

Solutions, AppSheet, Forms, No Code

TU5 Special: First Look at Building AppSheet Automations Using the New Google Forms Integration

Tags:
| | 0 Comments |

In this TU Special, we dive into the exciting new features coming to Google AppSheet and take a first look at the new Google Forms integration. For this special our co-host, Martin Hawksey is joined by Steve Franks (Head of Workspace Engineering, CTS Appsbroker). The discussion/demo was originally presented as an internal tech talk conducted at CTS Appsbroker – warning the episode contains some ‘passionate’ language.

Initial key takeaways which we discuss are:

  • Seamless Data Collection: Google Forms now acts as a first-class data source for AppSheet, eliminating the need for workarounds and additional add-ons. Data collected through forms automatically flows into AppSheet, creating a smooth and efficient workflow.
  • External Participation: The integration allows external parties to interact with your apps by simply filling out a Google Form. This opens up new possibilities for data collection and process involvement.
  • Powerful Automations: AppSheet’s automation capabilities can be triggered by form submissions, enabling you to create sophisticated workflows. The example discussed involves sending an email with an embedded AppSheet view upon form completion, allowing for approvals or further actions within the email itself.
  • Simplified Development: The integration makes building apps even easier, especially for those already familiar with Google Forms. The need for complex configurations or AppScripts is minimized, empowering users to focus on the business logic and desired outcomes.
  • Versatility: AppSheet has increased functionality, opening up a wider range of possibilities for app development and automation for Google Workspace customers. It can be used to create full-fledged apps with user interfaces or simply act as a behind-the-scenes automation engine.

Overall, the integration of Google Forms and AppSheet should significantly help empower users to build powerful solutions without coding expertise. Read more about “Build AppSheet automations using Google Forms” on the Google Workspace Updates blog.

Source: TU5 Special: First Look at Building AppSheet Automations Using the New Google Forms Integration

Gotchas, Forms, Sheets

Avoiding Google Apps Script onFormSubmit simultaneous executions when unlinking/linking Google Sheet responses

Tags:
| | 0 Comments |

Caution when using onFormSubmit triggers to avoid mass simultaneous executions when un/linking a Response Sheet.

The following post is a caution around onFormSubmit triggers after I recently discovered a way that somebody had inadvertently managed to execute my code simultaneously over 253 times. Now unless a Google Form is submitted by 253 individuals all at the same time … then this is baffling.

When should an onFormSubmit trigger run?
At the point when a Google Form has been completed and the ‘Submit’ button pressed.

How else can the trigger be activated?
Well … it turns out when you unlink and then link back a Google Response Sheet containing the onFormSubmit trigger, it will activate it for every single Form response collected up to that point in time …

Source: The Gift of Script: onFormSubmit Trigger simultaneous executions

Snippet, Beginner, Forms

Manage Google Form onFormSubmit script executions with Script Lock

Tags:
| |

Use the Apps Script Lock Service to control Form submissions and prevent data loss

Lock Service code snippet

Lock Service code snippet

The following Google Apps Script is a one example of how the Lock Service can be used to prevent concurrent running of code. Here we have a Google Form that can be submitted by users at any point, the code then takes some of those details and appends them to another Google Sheet row. In normal circumstances this will happen relatively quickly and without clashes, but what if multiple people submit the Form at the same time!?

[Editor note: An alternative approach to tryLock() is waitLock(). The only different with a waitLock() is it will throw an exception after the set number of milliseconds. An example of waitLock() with onFormSubmit is included in the reference documentation]

Source: The Gift of Script: Control Form submissions with Script Lock

Solutions, AI, Forms, Sheets, Beginner

How you can use Google Forms, AI, and Apps Script automation to analyze 1,700 survey responses (and the 1,000th AppsScriptPulse post)

Tags:
| | 0 Comments |

This post describes how I designed and ran an audience survey with over 1,700 responses, using Google Forms, Sheets, Apps Script, and ChatGPT. I’ll show you the entire process from end-to-end, including how I:

  • Created a survey with Google Forms
  • Used Apps Script to automatically say thank you to 1,700 respondents
  • Analyzed the response data in Google Sheets
  • Used AI to help me understand the qualitative data
  • Presented the results in Google Docs

It’s rather fitting that the 1,000th Pulse post features content by the one and only Ben Collins! Back in late 2019, when I was thinking about creating a new community site for Google Workspace developers, Ben’s encouragement was the spark that ignited AppsScriptPulse.

And today’s post by Ben is a nice example of Apps Script’s power to automate repetitive tasks. As part of this he shows how to craft personalised “thank you” emails for Google Form survey response with Google Apps Script. Ben’s insights go beyond ‘thank-you’s as he outlines how he administers and analyses customer surveys, highlighting his design choices for Google Forms and data analysis using built-in Google Sheets functions.

To take things a step further, Ben also highlights how he used ChatGPT to categorize qualitative survey responses. With Google’s recent announcement of their new AI model, Gemini, which outperforms ChatGPT  in a number of academic benchmarks, it would be interesting to see how these two platforms compare for this type of analysis.

Raising a glass (or an espresso :) to Ben and this 1,000-post milestone!

Source: How To Analyze Google Forms Survey Data with AI and Apps Script

Solutions, Chat, Forms, Sheets, Intermediate

Adding users to Google Chat spaces on demand with Google Apps Script

Tags:
| | 0 Comments |

Image credit: Pablo Felip

A Chat space membership authorization workflow built using Google Forms, Sheets and Apps Script. … In the coming sections, we’ll build a workflow for process managers to review and authorize membership requests sent by users willing to join a predefined set of chat spaces.

Lots of talk about Meta’s new Threads … is there an API for that. Meanwhile Pablo Felip has been busy putting together this very comprehensive tutorial which explains how you can use Google Forms as part of a process to administer Google Chat Spaces membership.

The API call spaces.members.create is relatively straightforward when compared to the amount of effort to setup the Google Cloud project. This is all covered in the source post and the code provided can easily be modified to suit other worksflows.

Source: Adding users to Google Chat spaces on demand with Apps Script

Snippet, Beginner, Forms

Auto close Google Form after X responses with Google Apps Script

Tags:
| | 0 Comments |

The following Google Apps Script is designed to automatically close a Google Form once it has reached the number of responses you specify.

Use Apps Script code to automatically close a Google Form

Use Apps Script code to automatically close a Google Form

Do you ever create a Google Form and then forget to close it? Or maybe you want to limit the number of responses that a form can receive? If so, you can automatically close a Google Form once it has reached the number of responses you specify with this example Google Apps Script.

Source: The Gift of Script: Auto close Google Form after X responses

Solutions, Docs, Forms, Sheets, V8, Advanced

The Ultimate Google Apps Script Front-End Development Guide. Part 0: The Worst and the Best Practices

Tags:
| | 0 Comments |

Welcome to the Ultimate Google Ultimate Google Apps Script Front-End Development Guide!

If you are reading this post, then you likely have experience with Google Apps Script. Essentially, there are three types of front-ends that you can create with Apps Script:

  • Web apps
  • Modal/modeless dialogues
  • Sidebars

In this blog post series, we will only discuss HTML front-ends, as this is where you can create the most powerful and sophisticated user experiences. Unfortunately, the Card Service is outside the scope of this series, and it is not nearly as powerful as an HTML front-end. With that in mind, Google, how about allowing HTML front-ends for Workspace add-ons?

Source: The Ultimate Google Apps Script Front-End Development Guide. Part 0: The Worst and the Best Practices