AppsScriptPulse

Solutions, V8, Advanced

Google Apps Script: The Secret to lightning-fast XML parsing with the Apps Script Engine (ASE)

Tags:
| | 0 Comments |

I increased Apps Script’s performance 10x

Recently, I worked on a project with a client who needed to parse XML files and convert them to JSON. Simple enough, right? The catch? These XML files were massive — each containing hundreds or even thousands of trades. Naturally, the first thing I thought of was Google Apps Script’s built-in XMLService. It’s reliable, widely used, and integrates smoothly into Apps Script. But when I ran it on a file with 1,000 trades, the parsing took 45 seconds! That’s painfully slow for an automation process.

[Editor: In a recent blog post, Dmitry Kostyuk has shared a valuable tip for developers working with Google Apps Script. When faced with the task of parsing large XML files, he discovered that the built-in XMLService was causing significant slowdowns. For files containing thousands of records, the parsing process could take an agonizing 45 seconds.

Dmitry’s solution? Using his Apps Script Engine (ASE) and the xml-js NPM library. By making this switch, he was able to achieve a remarkable 10x increase in parsing speed, reducing the 45-second process to only 4 seconds.

If you’re working with Google Apps Script and large XML files, click the source post link to find out how you can give ASE and xml-js a try!]

Source: Make Apps Script Fast Again! With Apps Script Engine

Snippet, Solutions, Docs, Intermediate

Enhance your Google Docs skills with these Google Docs API snippets for Google Apps Script

Tags:
| | 0 Comments |

Google Apps Script offers Document service for basic document tasks and Google Docs API for advanced control, requiring more technical expertise. This report bridges the gap with sample scripts to unlock the API’s potential.

Kanshi Tanaike’s latest blog post, “Unlocking Power: Leverage the Google Docs API Beyond Apps Script’s Document Service,” is a great read for Google Workspace developers. It offers a goldmine of code snippets that will supercharge your Google Docs projects. Tanaike highlights the advantages of using the Google Docs API for more advanced features and flexibility compared to the standard Apps Script DocumentApp Service. Even though the API requires a bit more technical know-how, Tanaike provides clear, practical examples to get you started.

Some of the sample snippets include:

  • Changing page orientation (portrait to landscape and vice versa)
  • Merging table cells
  • Creating and managing footnotes
  • Highlighting superscript numbers in footnotes
  • Resetting borders and shading
  • Creating checkbox bullets
  • Changing section margins
  • Controlling table row overflow
  • Setting custom line and paragraph spacing
  • Replacing placeholders spanning multiple paragraphs
  • Converting between soft and hard line breaks
  • Managing table borders
  • Creating nested lists
  • Retrieving all URLs from a document

Kanshi Tanaike’s post serves as a valuable resource for developers looking to expand their Google Docs capabilities. Hopefully with these sample scripts, you can start unlocking new levels of automation and customization in your Google Doc projects.

To delve deeper into the world of Google Docs API, follow the source link post.

Source: Unlocking Power: Leverage the Google Docs API Beyond Apps Script’s Document Service

Solutions, Advanced

Optimising Google Apps Script: Efficiently handling large datasets with iterators and generators

Tags:
| | 0 Comments |

We’re all used to dealing with iterables such as Arrays, Maps and Sets. Creating a collection of input items, transforming then outputting is what we do all the time. However, sometimes that’s not possible – for example, where we have a potentially large input data set of unknown size that would be too big to fit in memory, or perhaps an output method with rate limits. Dealing with items 1 by 1, despite being by far the simplest to code, is often the least efficient to execute – so we use a paging method to get an input chunk at a time, and a bulk method to flush out the transformed results.

All of that can get a bit tricky, especially if you add asynchronicity into the mix.The use of iterators and generators can make coding as simple as 1 in -> 1 out, while handling chunking and bulking automagically. In this article, we’ll look at how to implement these.

In this blog post, Bruce Mcpherson explores the use of iterators and generators in JavaScript and how these can be used in Google Apps Script. A generator is a special type of function that can be paused and resumed at any point, yielding values one at a time. This is unlike regular functions that return all their results at once. Generators can be useful for handling large datasets efficiently by processing items in chunks and outputting results in bulk, even when dealing with asynchronous operations.

While Apps Script doesn’t natively support generators, Bruce has found a handy converter published by Facebook to make them compatible. As part of the post Bruce includes some Apps Script samples you can test and modify, demonstrating how to apply these techniques in real-world scenarios, even within the limitations of the platform. Bruce provides clear explanations of these concepts, illustrated with practical examples like finding breweries near specific locations and well worth spending the time to digest.

Source: Paging large data sets and how to make Apps Script understand generators – Desktop liberation

Community News, Official News

Calling all Google Workspace Developers! Your feedback is needed

Tags:
| | 0 Comments |

Steven Bazyl, Head of Google Workspace Developer Relations, recently distributed a survey aimed at getting feedback on the Google Workspace platform. This is your chance to influence the future of the platform.

The survey is short and focuses on key areas like:

  • Overall satisfaction with Workspace Platform as a development environment
  • Your experiences with specific Workspace components (Meet, Chat, Gmail, Drive, Editors)
  • The biggest challenges you face in Workspace development
  • Your suggestions for improvements

By sharing your insights you have an opportunity to help shape the platform to better meet your needs and make your development workflows more efficient. I would encourage you to complete the survey (it only takes a few minutes) and share with other Google Workspace developers.

Don’t miss this opportunity to make your voice heard as this is the final week that the survey is open!

Survey link: Complete the Workspace Platform Developer Survey
LinkedIn post: Google Workspace user group post

Solutions, Drive, Sheets, No Code

Quickly append file permissions in Google Drive with Google Sheets and Google Apps Script

Tags:
| | 0 Comments |

Quickly append new permissions to existing files within Google Drive. Insert email addresses using a comma-space format with optional notifications.

Quickly append new file permissions via a Google Sheet

The following Google Apps Script tool allows you to quickly append new permissions to existing files within Google/Shared drive. This is quite a niche tool so it is anticipated you would already have a Google Sheet of file IDs/URLs, though there is the option to select an existing Drive folder of files and automatically bring them into this tool.

By inserting email addresses using a comma-and-space format you can optionally select to send a notification email to the new user(s)

You can continue to append further rows to the Google Sheet as existing ones will be skipped once the ‘Status’ column has automatically been marked as ‘Completed’. Alternatively you can clear the Sheet each time for a fresh start.

Source: The Gift of Script: Append Drive file permissions Tool

Solutions, Sheets, Intermediate

Effortlessly integrate WooCommerce customer data in Google Sheets with a little Google Apps Script

Tags:
| | 0 Comments |

Learn how to easily export your WooCommerce customers email, name and address to Google Sheets using Google Apps Script. The script will create a new tab in your Google Sheet and copy the data from the WooCommerce customers table.

If you are a WooCommerce store owner you might be interested in this solution by Amit Agarwal who provides a step-by-step guide on how to export WooCommerce customer data (email, name, address) to Google Sheets using Google Apps Script.

No more manual CSV exports or complex integrations, simply copy the provided script and follow the setup instructions. With this solution you can enhance your workflows, gain insights, and enhance your customer outreach. If you are not an existing WooCommerce user a solution for developers to keep in mind if you are seeking to optimise your Google Workspace add-ons customer experience.

Source: How to Export WooCommerce Customers to Google Sheets – Digital Inspiration

Community News

Gassypedia October 2024 Update: Apps Script Ecosystem Trends on Github

Tags:
| | 0 Comments |

Time for an occasional update on the shared BigQuery dataset all about the Apps Script ecosystem on Github, with reports available on Looker Studio.

Bruce Mcpherson has created an October 2024 update report on Gassypedia, a shared dataset that tracks the Google Apps Script ecosystem on Github. The data is publicly available on BigQuery and Looker Studio if you want to explore yourself, but Bruce has also provided this report on trends in Apps Script usage.

Key findings from the October 2024 update include:

  • Growth in shared code and developers: There has been a 7% increase in the number of developers detected and a 6% increase in the number of repos and manifests shared.
  • Rise of Sheets Add-ons: A 10% increase in total Add-ons shared, with Sheets overtaking Gmail as the most popular type.
  • Increased advanced service usage: Drive remains the most popular advanced service, with an 11% increase in usage.
  • Library usage insights: The most popular library is oauth2, with 235 distinct users. Identifying library creators is challenging and current methods are not 100% reliable.

The report also includes detailed breakdowns of developer profiles, geographic locations, repo contents, manifest profiles, OAuth scope usage, and library usage. Overall, the data suggests that Apps Script usage on Github is growing, with more developers sharing their code and creating add-ons, web apps, and data studio connectors.

Click through to read more and see who, where and what some of the Apps Script community have been up to…

Source: Gassypedia – October 2024 update – Desktop liberation

Solutions, Sheets, No Code

Unlock the power of charts: Get your free Google Sheets visual vocabulary template from Ben Collins 

Tags:
| | 0 Comments |

Enhance your Google Sheets skills with this Visual Vocabulary template. Learn how to choose the right charts, create effective visualizations, and design professional-looking dashboards. This comprehensive guide includes 31 common chart types, tips for data organization, and step-by-step instructions for using the SUBTOTAL formula to create interactive “Show/Hide” rows. Perfect for data enthusiasts and spreadsheet users of all levels!

Friend and fellow GDE, Ben Collins, has recently announced a new ‘Sheets Insiders’ membership program where he’ll be sharing some exclusive members-only newsletters, new templates, deep-dive tutorials, and more for both Google Sheets and Apps Script.

If you would like to ‘try before you buy’ Ben has shared the first issue of the Sheets Insiders membership program, featuring a Visual Vocabulary template for Google Sheets. The template is designed to help you in chart selection and the accompanying videos include lots of great tips to help you cleanly format your spreadsheets.

Future Sheets Insiders issues will delve into chart tricks, dropdown menus, interactive elements, AI and Sheets integration, formula challenges, and Apps Script content. The newsletter content will be archived in the Sheets Insiders Content Library for future reference. Click through to read more about Sheets Insiders and get your copy of the Google Sheets Visual Vocabulary Template.

Source: Sheets Insiders 1: Visual Vocabulary Template

Gotchas

Optimizing Google Apps Script: Minimizing latency with Google Cloud data regions

Tags:
| | 0 Comments |

Ping results to Google Cloud regions and short code snippet demonstrating how to measure latency from Google Apps Script.

As Google Apps Script is a software as a service, writing efficient code is crucial, especially for complex solutions. It’s also important to consider marginal gains, such as finding small ways to improve your script’s responsiveness.

If you’re using Google Cloud services like Vertex AI, Cloud Functions, or others, you’ll also want to consider the latency between your Apps Script code and the Google Cloud data region. Justin Poehnelt recently revisited the work of Ivan Kutil, providing a way to measure Google Cloud region latency from your Apps Script project.

Interestingly, when I ran Justin’s sample code, I found that the region with the lowest median latency (us-east1) was approximately 11 times faster than the region with the highest (asia-south2). You can make a copy of this sheet to view my results and run the test for yourself.

Source: Google Cloud Region Latency in Google Apps Script | Justin Poehnelt

Snippet, Video Tutorial, Sheets

Check comma separated email address is valid

Tags:
| | 0 Comments |

Loop through a Google Sheet cell of comma-space separated email addresses and check their format is valid, otherwise display a user popup.

The following Google Apps Script is designed to loop through a Google Sheet cell of email addresses that have been separated by a comma and space. It then uses a regular expression (regex) to confirm the email address meets the correct formatting criteria.

This code was developed as a way of implementing additional checks when asking users to be precise in how exactly they enter multiple email addresses. So if they were to forget the space for instance it could alert them, before the rest of the code risked failing as whatever task it was designed to do.

Source: The Gift of Script: Check comma separated email address is valid