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’
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!
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
See how AI tools work with Google Sheets to boost your productivity. Covers ChatGPT, Google Bard, and AI add-ons.
A very informative post from Ben Collins, discussing how AI can be used to automate tasks, identify patterns, and make predictions in the context of Google Sheets. Ben provides several examples of how Generative AI can be used to improve the way you can interact with data in spreadsheets from helping with formula to generating and improving data.
As Ben points out in the post users have benefited from AI in Google Sheets for a number of years with features like Explore. Perhaps the biggest recent change is exposing Large Language Models as a service, with users able to directly access the ‘prompt’ to generate output.
Bulk create Google Drive folders with multiple subfolders. Control the naming of the folders and how many you want.
Use this tool to bulk create folders with subfolders
The following Google Apps Script is designed to bulk create Google Drive folders with multiple subfolders. You control the naming convention of each folder and exactly how many you want.
Features include:
Runtime control – currently set to 5 minutes 30 seconds. Will ignore rows that have a ‘Folder Link’ so you can continue from where you left off and append further folders should you require.
More subfolders – the Google Sheet has columns for 10 subfolders but you can technically add more (columns) and the code will account for this automatically.
Toast popups to inform you of the progress as folders are created.
‘Log’ sheet and popup error messages if something goes wrong.
Concatenation – create those useful descriptive folder/file names so items are easier to search for in the future. Concatenate will let you combine values that may exist in different columns in another spreadsheet for example, or take those folder names and prepend/append words around them.
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
A Chat space membership authorization workflow built using Google Forms, Sheets and Apps Script. … In the coming sections, we’ll build a workflow for process managers to review and authorize membership requests sent by users willing to join a predefined set of chat spaces.
Lots of talk about Meta’s new Threads … is there an API for that. Meanwhile Pablo Felip has been busy putting together this very comprehensive tutorial which explains how you can use Google Forms as part of a process to administer Google Chat Spaces membership.
The API call spaces.members.create is relatively straightforward when compared to the amount of effort to setup the Google Cloud project. This is all covered in the source post and the code provided can easily be modified to suit other worksflows.
This report introduces the method for easily processing the template of Google Documents and Google Slides using Google Spreadsheet as a database using Google Apps Script. Google Spreadsheet is used as a database. Google Documents and Google Slides are used as templates. The simple method for creating new Google Documents and Google Slides using the database and the templates is introduced.
Latest Apps Script magic from Kanshi Tanaike, this time they turn their addition to an easy way to use data from Google Sheets as a ‘document merge’ for templates created in Google Docs and Google Slides. This is achieved by using the TemplateApp Apps Script library, which has lots of useful features including simply methods for sheetRangeToDocuments() as well as the ability to embeded and fixed width images from placeholders. There are some llimitations documented in the GitHub repo, as well as examples for all the methods available in the library.
The following Google Apps Script tool is designed to take a single Google Drive file e.g. a Doc / Sheet / Slide and make duplicates/copies of it with unique file names. The tool will also create a clickable link within the Google Sheet to each new file copy.
Screenshot of the tool for duplicating Drive files
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
If you frequently find yourself creating multiple Google Docs that follow a similar template and pull data from a Google Spreadsheet, this tutorial is for you. Today, I’m going to show you how to automate the process using Google Apps Script.
I liked the simplicity of this script which does a very basic data merge on Google Docs template from Google Sheets data. There is a bit of piecing together to get this one working but basically it’s uses a very simple replaceText pattern which maps the spreadsheet column number to replacement tokens {{col1}}, {{col2}}, etc. Read the source post to see all the code.
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.
