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.
Automate publication of your script from development to production easier.
This post by Stéphane Giron provides a CI/CD solution for Google Apps Script, which makes it easy for you to automate the deployment of Apps Script code from a development version to production. The solution uses the Apps Script API to manage versions and streamline the code copying process.
Stéphane has previously created a backup solution for Google Apps Script code, but it lacked version control and multi-file support. This new CI/CD solution addresses these limitations by using the Apps Script API to handle version management and the publication process.
The post includes the necessary code and configuration for implementing this solution. To use this solution you will need to enable the Apps Script API and as well as setting up your project with a standard Google Cloud Platform (GCP) Project.
As a bonus the solution also allows you the option of restoring previous versions of your deployed script, providing a rollback mechanism in case of errors or issues. This solution is lighter weight than other CI/CD setups featured in Pulse, but is easier to set up, providing a practical approach to implementing CI/CD for Google Apps Script projects and enhancing code management and deployment processes.
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)
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.
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.
It has finally happened! Bot Bender 2.0 is now available for installation from the Google Workspace Marketplace. In this post, I will walk you through the steps needed to get to the marketplace.
We’ve shared a couple of journeys to Google Workspace Marketplace publication but this is the first I’ve seen for publishing a Google Chat App. For people who have gone through the process it’s probably a familiar story of back and forth with the Marketplace review team.
In this post Anton Shevchuk shares how his Chat App received multiple rejects due to issues with the location of the support/privacy pages. The moral of the story is to be persistent, and don’t hesitate to push back if you believe the reviewers are mistaken. The post from Anton also outlines the other steps involved, including connecting the Google Workspace Marketplace SDK and configuring app visibility settings.
You can apply to join the Google Workspace Developer Preview Program to get early access to certain features. Features in developer preview have already completed early development phases, so they’re ready for implementation. This program gives you the chance to shape the final stages of feature development with feedback, get pre-release support, and have your integration ready for public use on launch day.
The Google Workspace Developer Preview Program (DPP) gives you early access to new features. If you are developing Google Workspace products and solutions, it’s a great way to stay ahead of the curve and provide feedback before feature release. Anyone can apply to join the DPP, just follow the source link, but note that you can’t enroll with a gmail.com account.
New Google Chart Card UI has just been added for buttons, chips, collapsible sections, and overflow menus. These additions extend the capability of Google Chat, allowing the possibility for users to interact with external tools and services without leaving the chat and continually having to context switch.
The public documentation page includes a list of all active developer previews. While these pages don’t offer visuals of the features, existing DPP members received a nice set of visuals in the email update. This example includes chips and a customizable control button for a collapsible section:
I’d highly recommend registering for the DPP so you can see what is coming in Google Workspace.
This report proposes a novel learning method using Gemini to automate Q&A generation, addressing the challenges of manual Q&A creation. By integrating with Google tools, this approach aims to enhance learning efficiency, accessibility, and personalization while reducing costs.
The rapid advancement of technology has offered both opportunities and challenges to the education sector. While technology can be a valuable tool for supporting teaching and learning, concerns about its appropriate use have existed for a long time.
The education sector is witnessing an increase in AI tools, each promising to enhance teaching and learning. However, the quality of these tools varies significantly. Some are better designed and can – if used appropriately – can be beneficial, while others are poorly designed. This can make it challenging for educators to find the right solutions that meet their specific needs.
This blog post by Kanshi Tanaike sheds light on the inner workings of some commercial AI tools developed for educators. It demonstrates how these tools can use generative AI to create multiple-choice questions (MCQs) on a given topic. In this particular solution, Google Forms are used in the process, with questions and answers automatically generated by the Gemini API for the user to answer in a Google Form.
For educators who have experience with Google Apps Script, this project looks like a useful starting point to refine and create your own solution or simply used to gain insights into the functioning of similar commercial tools.
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.
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.
