A Google Cloud run hosted puppeteer with a graphQL interface … In this article I’ll show you how to use this (I’ve called it gql-puppet ) with Apps Script
One of Apps Scripts superpowers is the ability to make HTTP requests to external services. These can be requests to other product APIs, or as in the following example, your own services. In this post from Bruce Mcpherson you can learn how you can use Google Apps Script to interact with a service he has created on Cloud Run which has a deployment of the headless browser, Puppeteer.
To interact with Puppeteer, Bruce has added GraphQL as an application interface. GraphQL is a query language that allows developers to request specific data from an API, making it more efficient and flexible than REST, which often requires multiple requests to retrieve the same data.
As Puppeteer is just a browser there are lots of things you can do like take a screenshot, print a PDF or get the page content. With GraphQL there is an interface layer to do this as well as returning specific data from a webpage.
The article has various code examples for different use cases, such as taking screenshots, extracting table data, and creating PDFs from web pages using Google Apps Script and saving the data in Google Drive. Bruce is providing access to his combined Puppeteer/Cloud Run/GraphQL solution, gql-puppet but you can also find out how you can deploy it as your own service.
This post describes a Google Apps Script function to create a dummy folder structure in Google Drive, including nested folders and files (Documents, Sheets, Slides). It highlights some useful features beginners might find useful such as recursion, randomness, and the use of Google Apps Script services for Drive interactions. The post additionally discusses specific challenges encountered, like using Drive.Files.create for file creation and ensuring compatibility with Shared Drives.
Working with Google Workspace Enterprise customers we recommend using a DEV/Test Google Workspace domain. If you purchase your Workspace licences through a Google Partner you should be able to get a free test domain, which admins can use to test features like Data Loss Prevention, Context Aware-Access without fear of breaking things for your live domain.
Unfortunately there are no convenient settings or data duplication so configuring your test environment can be a challenge. To help create some dummy data with a little help from Gemini I was able to create the following createDummyFoldersWithNesting() function:
To use the script
Copy into the Apps Script Editor
Enable the Google Drive Advanced Service
Configure the settings inside the function for the root folder and the number of files/folders to generate
Gemini suggested I highlighted the following key points:
Recursion: The createNestedFolders function calls itself to create folders within folders.
Randomness: The script randomly determines the number of folders and files to create, and the types of files.
Google Apps Script Services: It uses DriveApp and Drive.Files to interact with Google Drive.
File Metadata: When creating files, it uses the mimeType property to specify the Google Apps Script file type.
Whilst Gemini wrote a lot of the code for me (with a little guidance), there were a couple of gotchas I’ll highlight.
Drive.Files.create instead of DriveApp.createFile(name, content, mimeType) – the current documentation would suggest that you can use the .createFile() method and include a MimeType like GOOGLE_SHEETS, but as explained in this Stackoverflow post Google have said “After extensive consideration, we have determined that DriveApp.createFile() should not be used to create MimeType.GOOGLE_* files.”
I could have asked Gemini to rewrite this to use DocumentApp, SpreadsheetApp or SlidesApp .create() methods e.g. SpreadsheetApp.create() but then I would have to move into a folder, use extra scopes, which all felt a bit messy so instead opted for Drive.Files.create.
Drive.Files.create supporting Shared Drives without a blob – when using Advanced Services there is a bit of cross referencing required between the auto-complete in the script editor and the associated API documentation. For my script I wanted to support creating files in Shared Drive. To do this requires adding the supportsAllDrivesas optionalArgs in the Drive.Files.create(resource, mediaData, optionalArgs) method. As I only wanted blank Docs, Sheets and Slides I was scratching my head as to what to include for the mediaData blob. Fortunately this issue was discussed in the Google Apps Script Community – Google Group and it was clear I could use null or undefined.
An illustration which includes the Google Drive logo. The illustration includes a filing cabinet overflowing with files
This report provides a Google Apps Script to retrieve all files, including those within subfolders, for a designated folder. It addresses the challenges of retrieving files within deeply nested folder structures and obtaining complete file paths.
We’ve shared a couple of approaches to Google Drive reporting using Apps Script in Pulse, this latest comes from Kanshi Tanaike. There are actually two approaches covered in the post one using DriveApp and the other using the Drive API.
Hopefully, the related posts list on Pulse will highlight some of the alternative approaches you can use. I’m slightly obsessed with the .getFileNameWithPath() methods. This isn’t something that is provided in the Google Drive File resource data so it has to be constructed using only the file and parent information so it is interesting to see how this has been approached.
If you are looking at these code examples and struggling to understand them, Gemini (including implementations on gemini.google.com or aistudio.google.com), is a great way to copy the code (even the entire blog post) and start asking questions.
Surely, you have also experienced having a presentation in which you needed to replace old content with new. Replacing text is very simple because you just need to use the Replace function, and you can either do it in the Google Slides user interface.
The problem arises when you need to replace one image with another, for example, if your corporate logo is updated to a new graphic design or if one of your favorite cloud services updates its icons (Gmail, blink blink ;-) It’s still somewhat bearable with one presentation, but what do you do when, like me, you have thousands of Google Slides files on your Google Drive?
This post explores a clever application of the Gemini API’s multimodal capabilities, created by Ivan Kutil. His code utilizes GenAI to automatically detect outdated logos within your Google Slides presentations. The original blog post (March 2024) used the Gemini Pro Vision API. As a sign of how rapidly this area evolves, Google now recommends switching to Gemini 1.5 Flash or Gemini 1.5 Pro.
Switching to the newer APIs is very straightforward as all you need to do is search the source code for models/gemini-pro-vision and replace with models/gemini-1.5-flash or models/gemini-1.5-pro.
For Apps Script projects I lean towards Gemini 1.5 Flash as it is designed for speed. I’m also always looking to opportunities to test the GeminiApp library for Apps Script and very quickly I was able to fork Ivan’s code and use Gemini 1.5 Flash using a service account.
While GeminiApp requires some initial setup, it offers significant advantages:
Easy Model Testing: Experiment with various models quickly.
Built-in Features: Includes functionalities like exponential backoff.
In 2022, we made it easy to seamlessly access popular Education Technology tools directly in Google Classroom. We partnered with 20+ EdTech companies, including Kahoot!, Pear Deck, IXL, ReadWorks, and Nearpod, to build Google Classroom add-ons. These new integrations let educators and students easily find, use, and grade great content in their favorite EdTech tools without having to navigate to external websites and apps.
Today, we’re excited to make Classroom add-ons generally available to all developers.
Given the number of Apps Script users in EDU today’s announcement the Google Classroom add-ons now generally available for anyone to publish is probably welcome news. Whilst Google Classroom add-ons have been around for a couple of years they have historically only been created by a select few of Google partners.
Before jumping in something key to keep in mind is that while Apps Script offers a low-entry approach for most Workspace add-ons, Classroom add-ons require more technical expertise. They involve building a web application and integrating it with Classroom’s functionalities using the Google Workspace Marketplace SDK. Proficiency in web development languages like Python, Java, or Node.js is recommended and Google have published some example implementations for Python, Java and Node.js.
Whilst there are differences there are overlaps. For example like Workspace and Editor Add-ons you will need to create a Google Cloud project to publish to the Google Workspace Marketplace. As part of this you’ll have to also provide key information such as terms and conditions as well as going through the OAuth verification process.
The Google Developer documentation and other resources are linked from the source announcement.
This comprehensive guide walks you through connecting AppSheet to Google Cloud SQL, step-by-step. Unlock the power of a scalable and secure database to build powerful, data-driven mobile applications. Learn everything you need to know, from setting up Google Cloud SQL server, setting up the MySQL database as the datasource for AppSheet Apps, the connection to leveraging Cloud SQL for seamless data management in your AppSheet projects. If you are an AppSheet app creator or developer, this tutorial will likely be the best practice for you during the process of working with Google Cloud SQL
Google AppSheet GDE, Vo Tu Duc, has published a comprehensive guide on connecting AppSheet to Google Cloud SQL. Google provides a very useful support page on AppSheet data sources and performance, which highlights some scenarios where you might need to consider Cloud SQL.
For example, large-scale AppSheet apps using Google Sheets as a datasource with significant data access during specific times of the day, could encounter concurrency or quota issues imposed by the Sheets API. Using Cloud SQL you can configure your own instance configured to match your AppSheet app requirements avoiding any quota limits.
The guide provides very detailed step-by-step instructions for setting up Google Cloud SQL, creating a MySQL database, and connecting it to AppSheet. This includes lots of annotated screenshots, making it easy to follow even for those who are new to Google Cloud, MySQL, and SQL queries. Whilst the guide assumes you are creating a new app if you copy an existing app to an SQL database and then, if needed, upgrade the existing app.
One final thing to note is Cloud SQL isn’t part of the standard data integrations offered with AppSheet Starter and Core editions. As a result, all your app users will require AppSheet Enterprise licenses. Here is a summary of features that are supported with each subscription.
Create awesome Jira timelines for multiple projects in Google sheets – zmandel/timeline-jira-google-sheets
Viewing Jira timelines in Google Sheets can be beneficial in a number of ways including allowing you to use the features of Sheets for further analysis and exploration of the data. One such feature is Google Sheet timeline graphs. These can be customisable and let you configure timelines to display specific data, such as issue type, priority, or assignee.
To help with getting your Jira data into Google Sheets, Sig Mandel has recently published a Google Sheets template which you can copy and connect to your Jira data. Features of the template include:
Makes Timelines with Multiple Jira Projects – Create comprehensive timelines that encompass multiple Jira projects, enabling you to visualize and track the progress of related initiatives simultaneously.
Alerts When Issues Are Not Started or Ended on Time – Stay informed with timely alerts that notify you when issues are not started or ended according to their designated timelines. Proactively address potential delays and ensure projects remain on schedule.
View Timelines as Regular Sheets and as Native Timelines – View your timelines in two distinct formats: as traditional spreadsheets for detailed analysis and as visually appealing native timelines that offer a comprehensive overview of project progress.
One Click to View Issue Details or to Open in Jira – Seamlessly access issue details and navigate to the corresponding Jira issues with a single click. Quickly drill down into specific tasks to gain deeper insights and make informed decisions.
Works in “Epics & Stories” and “Stories & Subtasks” Modes for Issue Grouping – Customize your timelines by grouping issues based on “Epics & Stories” or “Stories & Subtasks.” This flexibility allows you to tailor the timeline presentation to your project’s unique structure and requirements.
Keeps All Previously Made Timelines for Easy Comparison – Maintain a historical record of all previously created timelines. Easily compare timelines to identify trends, progress, and areas for improvement over time.
Follow the source link to the GitHub repo, open the template preview link and click ‘Use Template’ to get started.
Search through Google Sheet data and correct the format of any date values to your choosing.
The following Google Apps Script is just one way you could search through data within a Google Sheet, pinpoint all date-values (no matter where they are), check their format (e.g. dd/mm/yyyy) and update it to a format of your choosing if necessary.
This function has been useful when American/British date formats have managed to make their way into a Google Sheet.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
As a Developer Advocate for Google Workspace, I live and breathe productivity tools. But even the most tech-savvy among us can have frustratingly simple oversights. My recurring pain point? Forgetting to turn on my Gmail vacation responder to automatically notify people of my absence before heading out of office.
Google Workspace Developer Advocate, Chanel Greco, has shared a clever solution to automate their Gmail out-of-office notification using Google Apps Script. Faced with the recurring problem of forgetting to set up the out-of-office notification, Chanel crafted a script that does the job based on Google Calendar events marked as ‘outOfOffice’.
Chanel’s project not only solves a personal problem but also demonstrates the benefits of Google Apps Script for task customization and productivity. By harnessing the power of Workspace APIs, you can create innovative solutions to automate routine tasks and streamline workflows.
