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

Snippet, Solutions, Docs, Sheets, Slides, Intermediate

Retrieve comments with emoji reactions from Google Docs, Google Slides and Google Sheet using Google Apps Script

Tags:
| | 0 Comments |

This report introduces the method for retrieving the Emoji reactions from the comments in Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) using Google Apps Script.

Here’s a clever workaround by Kanshi Tanaike for retrieving comments with emoji reactions in Google Docs, Slides, and Sheets using Google Apps Script. The process to achieve this is a little convoluted in that Google Docs, Slides and Sheets are exported in Microsoft equivalent formats, then re-imported into Google formats. To remove some of the pain the post includes sample code snippets for achieving this, which can easily be adapted.

Source: Retrieve Comments with Emoji Reactions from Google Documents, Google Slides, and Google Spreadsheets using Google Apps Script

Library, New IDE, Solutions

Creating a custom Google Apps Script project version history 

Tags:
| | 0 Comments |

This report introduces the method for managing the histories of the Google Apps Script project.

On August 23, 2023, the project history has been implemented in the new IDE of Google Apps Script. In the current stage, the users can see the history of the previously deployed script version. … In the case of the classic IDE, the users had been able to see the previously saved script version regardless of the deployment and just the save of the script. This is not implemented in the new IDE.

In August 2023, Google updated the online Apps Script Editor adding a feature which lets you view previously deployed script versions and compare them to the current script version. A current limitation the history is limited to versioned deployments. As noted in this source post from Kanshi Tanaike, deployments require several steps and you have to remember to go through the process. To make the process easier Kanshi has published a ScriptHistoryApp library, which can be used to create a custom web interface for making your own Apps Script project version history. Perhaps more usefully you can also manage snapshots of your script projects by fetching a URL. This makes it easy for you to either regularly save a project on a timed trigger or an event based mechanism.

Source: Managing History of Google Apps Script

Solutions, Sheets, Intermediate

Efficiently deleting rows by conditions in Google Sheets with Google Apps Script

Tags:
| | 0 Comments |

In this report, I would like to introduce a sample script for efficiently deleting rows by conditions on Google Spreadsheet using Google Apps Script. Recently, I had a situation for being required to achieve this situation. In my report, it has already known that when Sheets API is used, the rows can be efficiently deleted by a condition. Ref However, in that case, Sheets API couldn’t be used. Under this situation, I came up with a method. In this report, I would like to introduce this method.

Here’s a clever method from Kanshi Tanaike for deleting rows in Google Sheets based on a column condition. The solution makes use of the built-in .removeDuplicates() method, the clever bit is the script first copies the header row into any row that matches the condition. As this creates duplicate rows the .removeDuplicates() method can be called to the entire data range. Using this method Kanshi was able to improve an execute of an earlier function from 67 seconds to 13 seconds!

Source: Benchmark: Efficiently Deleting Rows by Conditions on Google Spreadsheet using Google Apps Script

Library, Intermediate

Easily managing time-driven triggers using Google Apps Script and the TriggerApp library

Tags:
| | 0 Comments |

Google Apps Script can be executed by time-driven triggers. This is one of the very important points for taking cloud computing. But, the scenarios using time-driven triggers are different for each user, and there are a lot of situations for using time-driven triggers. But, when a script for implementing time-driven triggers is developed, each script is different and complicated. In this report, I would like to introduce easily managing time-driven triggers using a Google Apps Script library.

In Google Apps Script time-driven triggers are a powerful way to automate tasks including Google Sheets. However, managing them can be difficult, especially if you have a lot of triggers and need triggers to run at different times and frequencies. The TriggerApp library from Kanshi Tanaike makes this a lot easier and this post includes a number of examples that are easy to copy/modify for your own script projects. The post covers the following scenarios:

  • Execute 2 functions at specific dates and times
  • Execute a function with a specific cycle between specific times between specific dates
  • Execute 3 functions with a specific cycle between specific times on weekdays
  • Send an email on a birthday every year
  • Execute specific functions on specific weekdays in a week
  • Execute 6 different functions every 10 minutes from “09:00:00” to “11:50:00” in order
  • Opening hours of Google Form from 09:00 to 17:00 on Weekdays
  • Opening Hours of Google Spreadsheet from 09:00 to 17:00 on Weekdays
  • Execute specific functions at 09:00 on Monday to Friday at the beginning of specific months

The source code for the library is on GitHub and includes documentation on all the methods it uses.

Source: Easily Managing Time-Driven Triggers Using Google Apps Script

Snippet, Solutions, Intermediate

Retrieving and putting values for PDF forms with Google Apps Script (and other PDF solutions)

Tags:
| | 0 Comments |

This is a sample script for retrieving and putting values for PDF Forms using Google Apps Script.

We’ve featured a couple of posts from Kanshi Tanaike on Pulse with solutions for handling/manipulating PDF Documents with Google Apps Script. This post on retrieving values from PDF Documents is just one in a series of recent contributions from Kanshi looking at how Google Apps Script can be used with PDFs:

All these examples use the PDF-LIB JavaScript library and as previously noted in the Pulse post Merging multiple PDF files as a single PDF and converting all the pages in a PDF to PNG images using Google Apps Script , with minor modification to can load this library into the Apps Script editor.

Source: Retrieving and Putting Values for PDF Forms using Google Apps Script

Community News, Library, Solutions, Sheets, Intermediate

Management of rich text cell formatting in Google Sheets with Google Apps Script

Tags:
| | 0 Comments |

In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. In this post, I would like to introduce the enriched management of rich text on Google Spreadsheet using Google Apps Script. In order to enrich the management of Rich Text using Google Apps Script, I created a library RichTextAssistant.

Some very clever work from Kanshi Tanaike which can help with the management of cell text formatting in Google Sheets.  The RichTextAssistant Apps Script library included in the post has some nice methods for both handling and preserving cell text formatting. It’s worth spend a little time checking the various samples provided to see what is possible and how these might enhance one of your own script projects.  If you are curious the source code for the library is also on GitHub and linked from the post.

Source: Enriched Management of Rich Text on Google Spreadsheet using Google Apps Script

Solutions, Intermediate

Generating a Time-based One Time Password (TOTP) in Google Apps Script

Tags:
| | 0 Comments |

In this post, I would like to introduce a sample script for putting Time-based One-time Password (TOTP) value into Google Spreadsheet using Google Apps Script.

For UK readers, no not Top of the Pops (TOTP), but Time-based One Time Passwords (TOTP). In the context of Google Workspace it’s more than likely that you can rely on the built-in Google Identity and account security features rather than rolling your own form of two-factor authentication (2FA). Should you be in such a situation when you need to authenticate outside of Google then this example integration of the OTPAuth library in Google Apps Script might be of interest. Rather than just updating a Google Sheet you could combine with MailApp/GmailApp to email the passcode for the user to use. See the source post for implementation information and caveats.

Source: Putting TOTP into Google Spreadsheet using Google Apps Script

Solutions, Intermediate

Google Drive folder picker using jsTree with Google Apps Script and Javascript

Tags:
| | 0 Comments |

This is a sample script for the folder picker using jsTree with Google Apps Script and Javascript.

The Google Picker is a file picker that allows users to select files from Google Drive which can be used in your Apps Script projects (See Using Google Picker with Google Apps Script). The Picker is good but there are certain scenarios where the functionality isn’t available, one I’ve personally found is selecting the My Drive or Shared drive root. Here’s an alternative approach from Kanshi Tanaike for an alternative folder picker created using jsTree. In the post you’ll find more information plus how you can use this solution with a service account.

Source: Folder Picker using jsTree with Google Apps Script and Javascript