AppsScriptPulse

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

Solutions, AppSheet, No Code

Discovering Google AppSheet in 48 hours | Part 3 – (Slice, dice, filtered and viewed)

Tags:
| | 0 Comments |

An intense 48-hour tour of AppSheet, Google’s flagship nocode tool, from the perspective of a newcomer to the platform (part 3/4).

It’s the third part of Pablo Felip’s AppSheet in 48 hours series. Christian Schalk has already post an announcement in the AppsSheet community which gives a great summary of this latest episode:

Here are some of key view centric topics covered:

  • A thorough introduction to Views, including their position (Primary, Menu, Reference), as well as user defined vs. and auto-generated. There’s also coverage on the new visual editing feature introduced in August!
  • Building custom views based on data slices, including coverage on performance implications between security filters and data slices.
  • As well as coverage on conditional formatting, including making view-aware expressions with the CONTEXT function.
  • And more!

The post is packed with lots of great tips, highlighting some of the key features and approaches for designing your AppSheet app.

Source: Discovering Google AppSheet in 48 hours | Part 3

Solutions, BigQuery, Calendar, Intermediate

Ghislain Sanjuan on LinkedIn: Google Admin and the raiders of the lost calendar (combining Google Workspace BigQuery logs and Apps Script)

Tags:
| | 0 Comments |

#GoogleCalendar gave me a funny adventure !

Here’s a fun and useful take from Ghislain Sanjuan explaining how you can restore deleted calendar events using Google Apps Script. To restore the events data is used from BigQuery Workspace Logs. For those unfamiliar Google Workspace Admins on supported editions of Workspace can setup a streaming export of activity data to BigQuery. This can be incredibly useful in situations where you need to review audit and usage data and in this scenario use it to restore data.

Source: Ghislain Sanjuan on LinkedIn: Google Admin and the raiders of the lost calendar

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, AI, Chat, Docs, Advanced

Tutorial: Respond to incidents with Google Chat, Vertex AI, and Apps Script

Tags:
| | 0 Comments |

Imagen: photo looking over the shoulder of a robot looking at a screen with chat messages and hand writing notes

This tutorial shows how to make a Google Chat app that responds to incidents in real time. When responding to an incident, the app creates and populates a Chat space, facilitates incident resolution with messages, slash commands, and dialogs, and uses AI to summarize the incident response in a Google Docs document.

Paraphrasing noted Google Workspace Developer Expert, Romain Vialard, GenAI has made Google Chat apps a tangible prospect. This tutorial from the  Google Developers site is a great example of how you can use Google’s Vertex AI with Google Chat. The tutorial will help you create a Google Apps Script powered Chat app that is able to summaries the messages in a Google Chat space.

There is a lot to take away from this example, but here are some of the headlines:

  • Setting a Google Cloud Project to use the new Google Chat Advanced Service for Apps Script
  • Setup and code for making calls to Google’s Vertex AI PaLM API (LLM) from Google Apps Script
  • Using the responses from Vertex AI to generate new assets.

There is a lot more you can do from this starting point, but hopefully it gives you a great starting point.

Source: Respond to incidents with Google Chat, Vertex AI, and Apps Script  |  Google for Developers

Solutions, AppSheet, No Code

Discovering Google AppSheet in 48 hours | Part 2 (Formula and Expressions)

Tags:
| | 0 Comments |

 

An intense 48-hour tour of AppSheet, Google’s flagship nocode tool, from the perspective of a newcomer to the platform (part 2/4).

The second part in Pablo Felip’s in ‘Discovering Google AppSheet in 48 hours’ series. This post focuses on the AppSheet formula language which are similar to formula/functions you would use in spreadsheets. As highlighted in Pablo’s post ‘expressions’ can be used in numerous places in AppSheet not only to calculate new values but also control your AppSheet app behaviour and functionality.

An area I often struggle with is handling data lists. Pablo’s post walk you through from basic list operations to more complex list filtering and list operations such as combining and subtracting data. This and more is covered in the posted linked to below:

Source: Discovering Google AppSheet in 48 hours | Part 2

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

Official News, Chat, Intermediate

Generally available: You can now call the Chat API from Apps Script with the Advanced Chat Service

Tags:
| | 0 Comments |

The Advanced Chat service lets you use the Google Chat API in Apps Script. This API allows scripts to find, create, and modify Chat spaces, add or remove members to spaces, and read or post messages with text, cards, attachments, and reactions.

There have been a number of updates to the Google Chat API summarised in the linked release notes page. A recent announcement which might interest Google Apps Script developers is the generally available of a new  Advanced Chat Service. As noted in the official documentation:

The Advanced Chat service lets you use the Google Chat API in Apps Script. This API allows scripts to find, create, and modify Chat spaces, add or remove members to spaces, and read or post messages with text, cards, attachments, and reactions.

There are some perquisites to use the Advanced Chat service besides the usual turning it on before use, the main one being the requirement to use a standard Google Cloud console project rather than the default project created automatically.

Source: Google Chat developer platform release notes  |  Google for Developers

Official News, Meet

Google Meet API is available in Developer Preview

Tags:
| | 0 Comments |

 

Image credit: Google

We’re happy to announce that the Google Meet API is now available for the members of the Developer Preview Program.

Google recently announced new Google Meet API capabilities are now available via the Google Workspace Developer Preview Program:

Launching through our Google Workspace Developer Preview Program, you can now use the Google Meet API. Specifically, among other things, you’ll be able to:

  • Programmatically create and configure Meet calls.
  • Retrieve meeting metadata, such as the meeting times and attendees.
  • Access meeting artifacts, such as transcripts and video recordings.
  • Subscribe to real time updates on meetings, such as when a participant joins or leaves.

The new API open the potential to both customise and get records from Google Meets. The source link provides an overview of the API and methods.

Source: Google Meet API overview  |  Google for Developers

Solutions, AppSheet, No Code

Discovering Google AppSheet in 48 hours | Part 1 (Data)

Tags:
| | 0 Comments |

An intense 48-hour tour of AppSheet, Google’s flagship nocode tool, from the perspective of a newcomer to the platform (part 1/4).

When introducing people to AppSheet I like to remind them that whilst it is a no/low code solution, like many other products it still requires you factor in time to learn and understand the product. Spreadsheets are a great example of this there being aspects that are relatively intuitive but other areas like formulas where it requires time to understand how to best use them.

I find learning vicariously, through the eyes of others, helps me understand what other people value in a product. This first post in a series by Pablo Felip in ‘Discovering Google AppSheet in 48 hours’ is a great example of just that. There are three more parts to come, but in part 1 Pablo sets the scene sharing initial impressions and first learning from a weekend in AppSheet.

Perhaps not unsurprisingly data features heavily in this first post. What you want your app to do will often be based on the data you already have or the data you will need. The post also contains this useful reminder:

Google AppSheet is a “core” service of Google Workspace. It is therefore covered by the same legal umbrella as the other core services of this platform with respect to terms of use, security and privacy of the data processed.

Pablo’s next part should be published next week and hopfeully like me you’ll be following along.

Source: Discovering Google AppSheet in 48 hours | Part 1