I’ve created a small (pseudo) class that more easily clears and creates conditional formatting rules in a Google Sheet tab with Google Apps Script. Why? Well in Google Apps Script, conditional formatting rules are all or nothing. You can only ever set, get or clear ALL rules in a specified Google Sheet tab.
Conditional formatting in Google Sheets can be a powerful tool. However, Google Apps Script’s native setConditionalFormatRules() method replaces the entire set of rules, requiring the retrieval and reconstruction of the complete rule array to add, modify, or remove a single rule. This can be cumbersome and inefficient.
Fortunately Scott Donald has developed Range_ConditionalFormatting(), a valuable utility that simplifies conditional formatting management. This includes a SetRule method that allows you to add new rules without overwriting existing ones, and the position parameter provides precise control over rule order, which is crucial as conditional formatting rules are applied sequentially.
Scott’s post includes all the code you need to get this working in your own project along with a number of example snippets. This makes managing conditional formatting much more efficient and manageable.
If you have domain-specific knowledge that you want an LLM to leverage, you probably have a use case for fine-tuning. Fine-tuning can significantly improve how well the model understands and responds to your queries, whether it’s legal documents, medical texts, financial reports, or niche industry data.
The most crucial step in this process is structuring your data correctly. If your dataset is well-organized and formatted properly, the rest of the workflow becomes much more manageable. From there, it’s just a matter of setting up a few configurations and automating parts of the process with Apps Script. That’s where things get interesting and surprisingly efficient.
Archive Google Sheet Data with Google Sheets API Advanced Service in Google Apps Script
Keeping your Google Sheets organised can be a chore. A recent tutorial by Scott Donald shows you how to automatically archive old rows based on multi-column parameters using Google Apps Script and the Advanced Sheets API, making data management easier and more efficient. This post highlights Scott’s approach to scripting a solution, including how to set up the Sheets API, the main components of the script, and potential applications.
As always Scott packs in a lot of useful tips and guidance; in particular, the tutorial highlights how to archive data using just five API calls, potentially making it faster than the standard SpreadsheetApp approach. Scott also details how to modify the script to suit different processes and includes details on his SsReq class. It provides a structured way to perform common operations like retrieving data, finding rows based on criteria, copying rows, appending rows, and removing rows, making it a great addition to your personal Apps Script toolbox.
For detailed explanations, code examples, and helpful tips, consult the complete tutorial.
Discover how to combine Imagen 3 and Google Sheets for rapid image creation. Automate your workflow and generate visuals in bulk.
Have you ever wished you could create a bunch of images without the hassle of typing in each prompt one by one? Well, Stéphane Giron has shared a clever solution using Google’s Imagen 3, Google Sheets, and Apps Script to streamline this process. While Gemini for Workspace is rolling out to more users and includes Imagen 3 for image creation in apps like Slides and the Gemini side panel, it doesn’t offer an easy way to create images in bulk. That’s where this method comes in. Instead of making images one at a time, this approach allows you to generate multiple images at once, saving you time and effort.
To get started with this method, you’ll need a Google Cloud Project and creating a service account (see the Build an AI slides reviewer with Gemini tutorial for instructions on setting this up). Once that’s set up, you create a Google Sheet with a column for your text prompts, and another for the links to the generated images. The images are automatically saved into a specific folder in your Google Drive.
The real magic happens with Apps Script. It uses Vertex AI to connect with the Imagen 3 model. The script reads each prompt from your sheet, sends it to Imagen 3 to create an image, and then stores the image in Google Drive. It also helpfully adds the image’s link back into the Google Sheet.
Check out the source post for the required code and instructions.
Let’s see whether Google’s AI Studio can teach me how to build a pivot table in Google Sheets. It’s wild how fast this technology is progressing and this gives us a glimpse into the near future when we’ll all have personal AI assistants helping us work more efficiently.
In a recent YouTube video, Ben Collins, a prominent figure in the Google Sheets community, explored the capabilities of Google’s AI Studio by using it to guide him through the process of creating a pivot table. This experiment provides a compelling glimpse into the future of how we might interact with software, suggesting that AI could soon enable conversational, real-time interactions that go beyond the traditional user-driven model.
Ben began by logging into Google AI Studio and initiating a real-time screen share of his Google Sheet, which contained real estate data. He then engaged the AI assistant, powered by Gemini, to help him create a pivot table to analyze this data. The initial request was straightforward: to see the sum of sales prices broken down by property type. The AI assistant demonstrated an understanding of this request and provided step-by-step instructions. The AI correctly instructed Ben to start by selecting any cell within his data and then navigating to the Insert menu to select “Pivot table”.
The AI assistant did stumble initially, incorrectly stating that the pivot table option could be found under the Data menu. This highlights an important point: while impressive, AI assistants are not yet infallible. As Ben pointed out in the video, that could be a stumbling block if someone didn’t know to look under the Insert menu. Ben’s familiarity with Google Sheets allowed him to identify and correct the AI’s misstep, and continue with the tutorial. This shows that even with sophisticated AI tools, a foundational understanding of the software is still essential.
Once the pivot table was created, the AI guided Ben through adding “property type” to the rows and “sales price” to the values section. It also prompted Ben to ensure the summarization of sales price was set to “sum” instead of “count” or another option. This highlights the AI’s ability to understand the nuances of data analysis in Google Sheets and guide users to the correct settings. This is a key insight, because the AI isn’t just providing instructions but it is also understanding the data context.
Ben’s experiment provides a vision of a future where AI agents become sophisticated collaborators within Google Sheets. These agents would not only provide step-by-step instructions, but could also actively carry out tasks, such as reformatting tables or creating charts and graphs based on conversational prompts. Imagine, for example, saying “reformat this table to be more visually appealing” or “create a chart showing sales trends over time” and having the AI make those changes automatically. This would move beyond current user workflows which depend on menu clicks, or even hand-written Apps Scripts, and would allow users to focus on high-level goals and analysis, rather than the mechanics of the software.
It is clear from the video that Google AI is an important area to watch for the future of Google Workspace. However, even with AI integration, it is still important to understand the tools you are using to ensure the advice you are receiving is correct. This is an important point, as it shows that AI should be seen as a helpful assistant, not as a replacement for user understanding. If you are interested in reading more about this I recently published an article on Empowering Enterprise Productivity While Preserving Critical Thinking.
When working with Google Sheets, you often use formulas like IMPORTRANGE to transfer data between files. However, IMPORTRANGE is not always reliable, especially when files become too large and you see the message “The file is too large.” In such cases, switching to a Google Apps Script-based solution that uses CSV files for data export and import is more efficient and flexible.
Here’s how you can create a system to export data from a Google Sheet to a CSV file and then import it into another sheet.
Export Data Script
This script exports data from a sheet named YOUR_TAB_NAME to a CSV file saved in Google Drive.
function exportToCSV() {
const SheetName = "YOUR_TAB_NAME";
const NameFile = "YOUR_FILE_NAME";
const ss = SpreadsheetApp.getActive();
const sourceSheet = ss.getSheetByName(SheetName);
// Get the values as displayed in the sheet
const dataValues = sourceSheet.getDataRange().getDisplayValues();
// Creating CSV content
const csvContent = dataValues.map(row => row.join(";")).join("\n");
// Check if there is data
if(csvContent == "") return;
try {
const fileId = DriveApp.getFilesByName(NameFile + ".csv").next().getId();
DriveApp.getFileById(fileId).setContent(csvContent);
} catch {
DriveApp.createFile(NameFile + ".csv", csvContent, MimeType.CSV);
}
}
How It Works
Fetches data from the specified sheet (YOUR_TAB_NAME).
Creates CSV content, joining data with the ; separator.
Updates the CSV file if it already exists or creates a new one.
Import Data Script
This script imports data from a CSV file into a Google Sheet named YOUR_TAB_NAME.
function importFromCSV() {
const SheetName = "YOUR_TAB_NAME";
const NameFile = "YOUR_FILE_NAME";
const Separator = ";"; // Change your separator if needed
const destinationSheet = SpreadsheetApp.getActive().getSheetByName(SheetName);
let fileId;
try {
// Search for the file and get its ID
fileId = DriveApp.getFilesByName(NameFile + ".csv").next().getId();
} catch {
Logger.log("Il file '" + NameFile + ".csv' non è stato trovato in Google Drive.");
return;
}
const file = DriveApp.getFileById(fileId);
const property = PropertiesService.getDocumentProperties();
// Check if the last imported data has already been loaded
const lastModified = property.getProperty("lastModified");
const timeStamp = file.getLastUpdated().toUTCString();
property.setProperty("lastModified", timeStamp);
if (lastModified == timeStamp) return;
// Retrieve the CSV content
const csvContent = file.getBlob().getDataAsString();
// Split the content into rows and then into columns using the specified separator
const data = csvContent
.split("\n") // Split into rows
.map(row => row.split(Separator)); // Split each row into columns using the specified separator
destinationSheet.clearContents();
destinationSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
How It Works
Finds the CSV file in Google Drive.
Checks for changes by comparing the file’s last modification time with a stored property value.
Reads data from the CSV file and imports it into the sheet, clearing existing data first.
Automation with Triggers
To automate the import process, you can use a time-based trigger to run the script at regular intervals (e.g., every minute, hour, etc.).
Setting Up a Trigger
Go to Apps Script Editor (Extensions > Apps Script).
Click on Triggers (Clock icon) or Tools > Triggers in the editor.
Create a new trigger:
Choose the importFromCSV function.
Select “Time-driven trigger.”
Specify the frequency (e.g., every minute).
With this trigger, your script will regularly check for updates in the CSV file and automatically import new data.
Explanation of the Separator Usage
Why the semicolon (;) is used: Descriptions or text fields may already use commas (,), and using them as a separator could lead to incorrect data splits. Using semicolons avoids this issue.
Alternative separator: If semicolons (;) are also present in the data, it’s recommended to use a unique symbol, such as §.
To update the separator, replace “;” with “§” in the following line:
.map(row => row.split("§"));// Update the separator here
This makes the script adaptable to various data scenarios.
Why Use This Method?
Advantages
Avoid IMPORTRANGE Limits: No errors related to overly large files.
Efficiency: Data is transferred as CSV, reducing connectivity issues between files.
Automation: Imports happen without manual intervention.
Limitations
Maintenance: Scripts need to be managed and updated manually if changes occur.
Security: Ensure file and script access is secure.
With these scripts and a configured trigger, you can reliably and efficiently handle large volumes of data between Google Sheets. If you need further customizations or help setting it up, feel free to ask!
Bluesky is gaining traction this end of 2024, and if you’re on the platform, you need to know your numbers. Want to track your Bluesky stats without the hassle? Google Apps Script and Google Sheets are here to rescue you with a simple, yet powerful solution.
In this blog post, Stéphane Giron provides a guide to tracking Bluesky social media statistics using Google Apps Script and Google Sheets. With the growing popularity of Bluesky it can be useful and interesting to understand the social media dynamic of the platform.
The script he offers tracks metrics such as the number of posts, followers, likes, reposts, and more. Stéphane also details how to install and set up the script, including copying and pasting the provided code, running initialization functions, and scheduling automatic data collection. The result is raw data that users can then use to create graphs and visualize their Bluesky activity over time.
Google Apps Script automates tasks like managing protections in Google Spreadsheets. These protections control user access to specific cells. While scripts exist for this purpose, users still encounter challenges, prompting this report. The report aims to introduce techniques for managing protections using sample scripts, helping users understand and implement this functionality.
Google Sheets aficionados are likely no strangers to the “Protect sheet” and “Protect range” options tucked away in the menus. These features offer a basic level of control over who can edit what within your spreadsheet. But what if you need more dynamic, automated control over these protections? That’s where the power of Google Apps Script and the Sheets API comes into play.
This post from Kanshi Tanaike provides a deep dive into how you can programmatically manage protections in your Google Sheets. While the traditional menu options are great for static scenarios, using Google Apps Script allows you to create more flexible and powerful protection workflows.
Why Go Script?
Dynamic Protections: Instead of manually adjusting protections, you can use scripts to change them based on specific conditions or events within your spreadsheet.
Automation: Integrate protection changes into larger automation workflows, streamlining processes and reducing manual intervention.
Granular Control: Achieve a level of control over cell-level permissions that goes beyond the standard menu options.
Some possible use cases for developers could include:
Approvals Automation: Imagine a scenario where certain parts of a spreadsheet need to be locked down once a manager approves them. With this solution, you could create a script that automatically protects those ranges upon approval.
Time-Limited Editing: Need to open up a section of a spreadsheet for editing only during a specific window of time? You could use Google Apps Script to handle this, automatically protecting
The scripts provided by Kanshi Tanaike offer a starting point for exploring these possibilities.
Quickly copy or move existing files into folders within Google drive via a Google Sheet.
Quickly copy or move existing files into folders via a Google Sheet
The following Google Apps Script tool allows you to quickly copy or move existing files into existing folders 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.
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.
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
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.
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