This video shows how to use the Palm API with Google Apps Script to extract data from Google Analytics 4 accounts. This can be useful for a variety of purposes, such as creating custom reports, automating data analysis, and building new data-driven applications.
Following on from the last post in Pulse where we looked at using Google PaLM API and MakerSuite in Google Apps Script, here’s another example from GDE Linda Lawton. As the video in the post shows Linda has been able to engineer prompts that allow you to use natural language to extract reports from Google Analytics. This shows the emergent capabilities of LLMs as well as some clever prompt engineering. The source post contains more detail, but here is an example:
var text = "The current date is '"+ date + "'. Create a JSON object which contains five parameter's dimension, metrics, start_date, end_date and property_id. The dimension and metric parameter's will be comma separated strings they can be empty if there is no valid text for it. The value of the dimension parameter should be a comma separated string of these dimensions names 'country, eventName, city, audienceName' and the value of the metric parameter should be a comma separated string of these metric names 'activeUsers, eventCount, screenPageViews', the property_id field will also be a string it will be a large number, start date and end date must be in the following format YYYY-MM-DD, which can be found in the given this text '" + prompt + "'. If no start date is found use set it to seven days ago and if no end date is found set it to today."
A couple of highlights worth noting:
Context – The current date is included programmatically to give the LLM a reference point
Reinforcement – ‘start date and end date must be in the following format YYYY-MM-DD’
Exceptions – ‘If no start date is found use set it to seven days ago and if no end date is found set it to today’
This post has covered how you can quickly copy MakerSuite code examples to run them in Google Apps Script. To make the iterative process easier I’ve created GenerativeLanguageApp so that once you’ve created/saved an API key, you can drop these code snippets into your Google Apps Script project:
Generative AI (GenAI) is a rapidly developing field that has the potential to revolutionize many industries, including the way we work. Google has developed a number of LLMs that are generally available to developers, including foundation models trained for text, chat and code which are accessible as part of the PaLM 2 API. To help developers explore the capabilities of these models, Google has created the MakerSuite site.
In this post I share GenerativeLanguageApp, a Google Apps Script helper class which makes it easy to quickly copy MakerSuite code examples to run them in Google Apps Script. This is a great way to start experimenting with LLMs and seeing how they can be used in Google Workspace.
In the next part of this series, I’ll explore some of the capabilities of when using the PaLM 2 API with data from Google Sheets. Stay tuned!
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!
I recently faced a frustrating issue when writing data to a CloudSQL database with the JDBC class in Apps Script. I kept getting the following error:
Exception: Argument too large: SQL
I also observed that it only happened when my SQL query reached a certain length. I considered breaking it down into multiple queries, but I was still puzzled 🤔 because I was only sending a few dozen kilobytes of data.
Now, the thing is, the official documentation could be more helpful; even though the solution is there, it needs to be better explained. So, I turned to StackOverflow. There was a discussion on this exact topic, but to my surprise, I was still waiting for an answer. Until, well, I wrote it 😉
If you have strong AppSheet technical skills along with solid Google Workspace skills and would like to share your expertise globally as a recognized “Google Developer Expert” (GDE), we would like to invite you to apply to become an official GDE member specializing in AppSheet and Workspace!
The Google Developer Experts program is a great way to get recognised for your abilities, but more importantly an opportunity to get close to the Google product teams to learn and share your day-to-day experiences. Whilst AppSheet is positioned as a no/low code solution it doesn’t mean there aren’t individuals out their developing sophisticated apps and supporting the community along the way.
If this sounds like you the AppSheet team are looking for community experts to become the next AppSheet GDEs. This source post includes some more information as well as a form where you can note your interested. As a GDE of 9+ years I’m also happy to share my experience of the program and what I think it takes to stand out from the crowd.
Learn how to seamlessly integrate JSON data from an API into Looker Studio using a custom connector built with Google Apps Script.
This post is a useful reminder of that Google Apps Script can be used to make a data connector for Google online visualisation and reporting tool, Looker Studio. The post by Dimitris Paxinos covers all you need to know about integrating a third-party API as a data source, exposing configuration settings and deploying the connector. All the code is on Github and is a great boilerplate if you have other APIs you are interested in integrating.
Learn how extract all the embedded images from a Google Document or Google Slides presentation and save them as individual files in a specified folder in your Google Drive.
Often I’ll use Google Docs for drafting blog posts. With the introduction of Duet AI having a generative assistant in situ helps with the creative process. As well as text Google Docs is a really simple canvas for quickly copy/pasting screenshots. Copying content from Google Docs to WYSIWYG editors can sometimes be challenging, particularly, when it comes to images.
Next time I encounter this problem I’ll be using the script solution from Amit Agarwal, which can extract images from Google Docs and Slides and save them to Google Drive. I’m sure there are many other situations where this snippet could come in handy.
Check a Google Sheet once per day and if the date is in the past Autofill another row of formulas.
Autofill Google Sheet formulas each day
The following Google Apps Script is designed to check a Google Sheet once per day and if the date is in the past it Autofills another row with the existing formulas used across the columns. This post is a variation of the Autofill Google Sheet Formula one.
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
We are excited to roll out to GA (General Availability) our first visual editing features to all AppSheet creators, starting today.
This a new way for AppSheet creators to make changes to their apps and navigate the editor. It’s intuitive for anyone to point at what they want to change if they can see it. We are now letting you do that in the editor: you can now hover on visual components of the editor’s app view and see possible editor actions for the outlined component.
Google recently announced the rollout of new new visual editing features in AppSheet. The new feature should help simplify and speed up app development (see the source post for an animated gif / video of what it looks like). In the announcement Google mention that the new feature isn’t available for all components:
Outlines are only available for some components visible in the app. For instance, Detail and Form Views have a lot more controls than the Deck View right now. Additionally, only some Editor actions are available, such as navigating users to Data components, View components, Action components and some of the general Settings.
More updates are planned and if you don’t see this feature in AppSheet yet the rollout has been paused for Google Cloud Next and should be available for everyone soon.
Unfortunately, there are no built-in methods for directly managing PDF data using Google Apps Script. Fortunately, after the V8 runtime has been released, several raw Javascript libraries could be used with Google Apps Script. pdf-lib is also one of them. When this is used, PDF data can be cooked over Google Apps Script. In this report, I would like to introduce achieving this using a Google Apps Script library.
Google Apps Script is a powerful tool for automating tasks. It can be used to process data, create spreadsheets, and send emails. One of the limitations of Apps Script is that it does not have built-in support for PDF files. However, there are a number of third-party libraries that can be used to work with PDF files and in Pulse we’ve featured a number of posts from Kanshi Tanaike where they have explored the pdf-lib is a JavaScript library.
This work has culminated in PDFApp, a dedicated Apps Script library created by Kanshi Tanaike based on the pdf-lib, but optimised for Apps Script. The source post includes a number of recipes for handling PDF files listed below:
