AppsScriptPulse

Solutions, Drive, Sheets, Intermediate

How to audit Google Shared Drive permissions in Google Sheets with Apps Script

Tags:
| | 0 Comments |

Are you looking for an efficient way to get an overview of all shared drives and their access permissions within your organization? Whether you’re navigating a company reorganization or implementing security procedures, accessing this information can be challenging.

This post from Niek Waarbroek highlights the important of auditing Google Shared Drive permissions. This can be a challenge to do using the Google Workspace Admin Console, especially if you have lots of Shared Drives.

To help Niek has shared a Google Sheet with Apps Script code that automatically generates a list of all shared drives and their associated root level permissions.

Shared Drive auditing is bit of a niche subject, but I encourage you to have a look at the post and script as it has some nice features which could be applicable to other projects. For example, there is a gaspTimeManager to make sure the script doesn’t go beyond the script execution limit.

Source: How to audit shared drive permissions in Google Drive

Solutions, Sheets, Beginner, No Code

The complete guide to Smart Chips in Google Sheets

Tags:
| | 0 Comments |

Image credit: Ben Collins

Learn how to use Smart Chips in Google Sheets to take full control of your data. Smart chips bring extra information to your Sheets.

Unleash the hidden power of Google Sheets with Smart Chips! Here’s a nice primer for our next episode of Totally Unscripted from Google Sheets magician, Ben Collins. You’ll have to tune in to the show to get the developer angle on Smart Chips, this post instead focusing on out-of-the-box features of Smart Chips in Google Sheets. Ben’s post is still incredible useful and in particular it was very interesting to read about data extraction from Smart Chips including the dot syntax when using Google Sheets formula/functions.

Follow the source link to find out more!

Source: The Complete Guide to Smart Chips in Google Sheets

Library, Solutions, AI, Gmail, Sheets

GenAI for Google Workspace: Exploring Gemini API Function Calling with Google Apps Script — Part 3

Tags:
| | 0 Comments |

 

Using Google Gemini API with Google Sheets to create personalized mail merges. An exploration in generative AI ‘function calling’

Google latest generative AI solution, Gemini, includes the capability to declare functions that the LLM can use in it’s response. The response includes the name of the function and the parameters the script needs to run the function.

The function isn’t executed by the LLM, but run with your code, which creates really interesting opportunities for Google Apps Script solutions. In particular, given user identity and authorisation is an integral part of Apps Script and how it integrates with other Google services it means solutions like personalised mail merges can be created in a couple of lines of code.

Follow the source link to find out more about function calling and exploring Gemini’s capabilities with data in Google Sheets.

Source: GenAI for Google Workspace: Exploring Gemini API Function Calling with Google Apps Script — Part 3

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, Sheets, Beginner

Google Sheets [🔧Fixed]. Prevent users from deleting formulas with Google Apps Script

Tags:
| | 0 Comments |

You’ve made a formula, but someone deleted it. It may be annoying to restore your formula. You are not always able to protect a range with formulas, as protection will also forbid users from sorting range, adding new rows, hiding rows, etc.

Let’s create our formula protection with the help of a few formulas and app script code.

Google Sheets are fantastic for collaboration, the downside however can be that other people can break stuff. The Protected Sheets and Ranges can help with this, but there might be times when you need an alternative solution. If you find yourself in this situation Max Makhrov has come to the rescue with a solution to rewrite formula if they are accidentally deleted. You can find out more in the source post link. As a bonus Max includes a named Google Sheets function, FormulasMap, which can be used to export functions in a range to another sheet.

Image credit:
Max Makhrov

 

Source: Google Sheets [🔧Fixed]. Prevent users from deleting formulas

Solutions, AI, Sheets, Advanced

GenAI for Google Workspace: Exploring the PaLM 2 API and LLM capabilities in Google Sheets — Part 2

Tags:
| | 0 Comments |

Imagen: An photo image which has a laptop with a spreadsheet application which appears to have rays of light

This is the second part exploring the GenAI capabilities in Google Sheets. In this part learn how you can make an Enhanced Smart Fill for Google Sheets

Google recently announced the latest feature for Duet AI for Google Workspace with Enhanced Smart Fill, which uses GenAI in Google Sheets to generate content based on data and the patterns entered by the user.

Continuing a previous post exploring the PaLM 2 API and LLM capabilities in Google Sheets, this post looks provides a Google Sheet template for experimenting with LLM prompts and spreadsheet data, including how you could make a ‘Enhanced Smart Fill’-like star review generator.

The post includes everything you need to get started, with you only having to make your own MakerSuite API key.

Source: GenAI for Google Workspace: Exploring the PaLM 2 API and LLM capabilities in Google Sheets — Part 2

Solutions, Sheets, Intermediate

How to write Google Apps Script logs into Google Sheets

Tags:
| | 0 Comments |

In Google Apps Script, the ability to track and record actions, errors, and performance metrics is crucial for both developers and users. However, the built-in logging mechanisms often fall short regarding accessibility and ease of use. This is where Local Google Apps Script Logging comes into play, offering a streamlined and integrated approach to capturing script activities.

Google Apps Script has a couple of logging options, including the native Apps Script execution log, to setting up a Cloud Developer Console project and using Cloud Logging and Error Reporting.  There are a couple of alternative Apps Script logging solutions out there, like Peter Herrmann’s BetterLog. Here’s the latest alternative Apps Script logging solution from Dimitris Paxinos called LocalLogger.

LocalLogger has some nice features including built-in severity colour coding and customisable email notifications. Even if you don’t need a alternative logging solution the code is well structured and includes a way to mimic an Enum list. You can find all the code and a video explaining LocalLogger via the source link 👇🏻

Source: How to write Google Apps Script logs into Google Sheets

Beginner, Sheets

Handling date objects between Google Sheets with different timezones using Google Apps Script

Tags:
| | 0 Comments |

This is a sample script for copying the date object between Google Spreadsheets with the different time zones using Google Apps Script.

As I’ve previously mentioned working with dates, times and time zones can often be a bit of a headache. If you’d like to learn more about some of the challenges of dealing with ‘big balls of wibbly-wobbly, timey-wimey… stuff’ I recommend watching Comptuerphile’s Problem with Time & Timezones.

This post from Kanshi Tanaike highlights a couple of approaches for handling date/time objects in Google Apps Script when you are using Google Sheets.

Source: Copy Date Object between Google Spreadsheets with Different Timezone using Google Apps Script

Official News, Docs, Sheets, Slides

New Smart Chips (including AppSheet apps) plus third-party @menu resources in the Google Workspace Editors 

Tags:
| | 0 Comments |

There have been a number of recent updates and announcements around Smart Chips and other integrations to Google Docs, as well as Sheets and Slides. Some have just gone into the Google Workspace Developer Preview Program (DPP), whilst in the case of AppSheet smart chips for Google Docs they are generally available!

AppSheet smart chips for Google Docs

Starting with AppSheet smart chips for Google Docs if you already had the AppSheet Google Docs add-on installed you already have AppSheet smart chips enabled. More details including a link to the Workspace Marketplace Add-on are included in the related Workspace Updates post. The AppSheet smart chips are great way to integrate some of your app functionality in your Google Docs.

Preview links with smart chips in Sheets and Slides

We’ve had custom smart chip link previews for Google Docs for some time, but Google have recently added the same functionality to Sheets and Slides. If you have already developed link previews for Google Docs the good news is there isn’t much more you need to do to enable these for Sheets and Slides (mostly updating your manifest). More details are included on the updated Preview links with smart chips documentation page.

Create third-party resources from the @ menu

Finally, also gone into preview is third-party resource integration which you can use to “let users quickly create resources, such as tasks, support cases, or issues, in your service right from Google Docs. The new resource is then inserted into Google Docs as a smart chip.”. You can read more in the create third-party resources from the @ menu documentation page.

In the case of the preview features a reminder that this is your opportunity to test and give feedback to Google. Links to join and provide feedback are included in the Google Workspace Developer Preview page. Enjoy!

Solutions, Drive, Sheets, Intermediate

Uploading files without authorizing scopes  with a dialog in Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

Making the shared users input a value and upload a file without authorization of the scopes with a dialog on Google Spreadsheet.

It’s usually unavoidable when you are creating and sharing Apps Script projects that the user will be required to complete an authentication flow to approve access to the services you include in your script such as reading/writing to Google Sheets, Drive etc.

The process is reliant on OAuth scopes, which are identifiers that specify the level of access an application requests from a user’s Google Account data. They are essentially a way for developers to define the specific actions or data their application needs to access. When a user grants an application access to their Google Account, they are agreeing to allow the application to perform the actions or access the data specified by the scopes.

Sometimes you can restrict the ‘scope’, for example, usually for Sheets, Docs, Slides, and Forms where I need only permission for the current doc I will include the following documented comment to only require access to the doc that the script project is bound to:

/**
 * @OnlyCurrentDoc
 */

There are some limitations when defining the scopes you need. For example if you would like a user to upload a document to Drive usually you would require the very broad https://www.googleapis.com/auth/drive scope which will prompt the user to ‘view and manage all of your Drive files’.

Understandably users may become nervous approving such a scope and in some cases Google Workspace Admins may prevent authentication for this type of scope for unverified/unconfigured applications.

There are alternative approaches to allowing users to execute Apps Script projects without having to approve scopes like Google Drive. There are clearly security considerations when you do this, so always proceed with caution.

This post from Kanshi Tanaike has some examples of how users can be prompted to upload files to Google Drive without authorising Drive access. The post includes two approaches, the first using a Web App which is pre authenticated to run as the user who has deployed the Web App, the other using a service account. The source post contains all you need to know include the code.

Source: Uploading Files without Authorizing Scopes by Shared Users with Dialog on Google Spreadsheet using Google Apps Script