AppsScriptPulse

Automate your Bluesky analytics with Google Apps Script and Sheets

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.

Source: Bluesky Analytics, Track your Stats with Google Apps Script and Google Sheets

🚀 Stuck on a Google Apps Script Challenge? Let’s Solve It Together!

So, let’s do something different. If you’re facing a technical challenge, I’m opening up a new way to get direct support. 🎯

How It Works: Fill out this Google Form and share the details of your challenge. Here’s what I’ll need from you:

In this blog post, Dmitry Kostyuk offers Google Apps Script users assistance with their coding challenges. Users can submit their problems through a Google Form, providing details about their problem, relevant code snippets, and any additional comments. Dmitry emphasizes that this is a collaborative learning opportunity, not a code-writing service. Submitted challenges might be showcased anonymously on Dmitry’s blog and social media. Follow the source link for more details.

Source: 🚀 Stuck on a Google Apps Script Challenge? Let’s Solve It Together!

Going beyond the menu: Programmatic controlling Google Sheets protection using Google Apps Script

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.

Source: Technique for Protecting Google Spreadsheet using Google Apps Script

The Transformative Power of Generative AI: Reimagining the Enterprise with Gemini for Google Workspace

This article was co-authored with Gemini for Google Workspace. You can read the full transcript of our conversation.

I appreciate this isn’t the typical Pulse post, but in my mind a lot of Google Workspace Developers who subscribe to this site have a wider interest in Google Workspace. Having worked with and supported a number of customers exploring Gemini for Google Workspace I thought it would be useful to sharing some of my thoughts and observations regarding the transformative power of Generative AI. As part of this I share insights around:

  • The importance of change management in adopting GenAI.
  • Mastering the art of prompting and interaction.
  • The need for continuous learning in a rapidly evolving landscape.
  • The future of human-AI collaboration.

Introduction

Imagine collaborating with an AI to draft a compelling marketing campaign, brainstorming ideas in a fluid conversation, and watching as it generates compelling copy and visuals in real-time. This is the power of generative AI tools like Gemini for Google Workspace, and it’s transforming how enterprises work.

My own journey with generative AI began in late 2023 when I had the opportunity to be part of Google Cloud’s trusted tester program. Working with early models in MakerSuite, Bard, and Duet AI gave me a glimpse of this transformative power. I was immediately struck by the shift to a conversational paradigm. We’re no longer just issuing commands to machines; we’re engaging in dynamic dialogues to create and innovate.

Having now supported several businesses with Gemini for Workspace ‘proof of value’ pilots, I’ve come to realize that simply introducing these powerful tools isn’t enough. To truly unlock the potential of generative AI like Gemini, organizations need a multifaceted approach. This includes investing in training, fostering internal “champions,” strategically identifying pain points for improvement, measuring success, and thoughtfully disseminating new ways of working. This is the key to successfully integrating this revolutionary technology into the enterprise.

The Promise of GenAI for Enterprise Productivity

McKinsey’s research paints a compelling picture of generative AI’s potential to revolutionize productivity. They estimate that generative AI could add the equivalent of $2.6 trillion to $4.4 trillion annually across the 63 use cases they analysed. This staggering figure underscores the transformative impact this technology could have on businesses across various sectors, from automating mundane tasks to unlocking entirely new levels of creativity and efficiency.

But the true promise of GenAI extends far beyond simply saving time. Tools like Gemini empower employees to:

  • Improve quality: By providing intelligent suggestions, generating alternative approaches, and catching errors, GenAI helps elevate the overall quality of work.
  • Upskill and learn: Gemini not only provides answers but often explains the “why” and “how” behind them, offering valuable learning opportunities and fostering deeper understanding.
  • Conduct advanced analysis: With the ability to analyse vast amounts of data, GenAI unlocks new levels of insight and understanding that were previously impossible.

Just this week, I was working with a pilot participant from a company’s People team. They needed to analyse an employee satisfaction survey with hundreds of free-text responses. Imagine the task: categorizing those responses by theme, extracting key quotes, and generating a comprehensive report with recommendations. Gemini was able to analyse all the responses and create a draft report in minutes – a task that would normally take a full day. While the person still needed to check the data and refine the report, having this head start, complete with accurate thematic categorization and relevant quotes, significantly enhanced their efficiency and allowed them to focus on higher-level analysis and recommendations.

These benefits highlight the transformative potential of GenAI to not just speed up work, but to fundamentally enhance the way we work and the value we create.

The Importance of Change Management

Introducing a powerful tool like Gemini into an organization is more than just adding a new piece of software; it requires a fundamental shift in how people work and think about their roles. This is where change management becomes crucial. The ADKAR model, which focuses on Awareness, Desire, Knowledge, Ability, and Reinforcement, provides a useful framework for navigating this transition.

  • Awareness: First, employees need to be aware of what Gemini is, its capabilities, and how it can impact their work. This goes beyond simply showcasing features; it’s about communicating the ‘why’ behind the change and painting a clear picture of how Gemini can benefit them individually and the organization as a whole.
  • Desire: Next, we need to foster a desire to use Gemini. This involves addressing concerns, highlighting the advantages, and creating a sense of excitement about the possibilities. One of the biggest hurdles is overcoming the mindset of ‘I need to do this myself.’ Many employees have never had the experience of having an assistant, so it’s about encouraging them to embrace Gemini as a valuable partner that can augment their capabilities and free them to focus on higher-value tasks. In one of my pilot programs, an employee was hesitant to use Gemini in Gmail because it felt like ‘cheating’ to spend less time writing emails. To address this, I explained that Gemini wasn’t about cutting corners; it was about freeing up time for more valuable activities. By using Gemini to generate a first draft, the employee could then dedicate more time to refining the message, adding personal touches, and ensuring the communication was truly impactful. This reframing helped the employee see the value of GenAI not as a replacement for their skills, but as a tool to enhance them.
  • Knowledge: Once the desire is there, employees need the knowledge to use Gemini effectively. This involves providing comprehensive training on the tool’s functionalities, best practices for prompting, and strategies for integrating it into their workflows. It’s about equipping them with the skills and understanding to truly leverage Gemini’s potential.
  • Ability: Knowledge is not enough; employees need the ability to apply what they’ve learned. This requires hands-on practice, opportunities for experimentation, and ongoing support. It’s about creating a safe environment where people can explore, make mistakes, and learn by doing.
  • Reinforcement: Finally, to ensure lasting adoption, reinforcement is key. This involves recognizing and rewarding successful use of Gemini, providing ongoing support and resources, and continuously communicating the benefits and impact of the tool. It’s about creating a culture where GenAI is seamlessly integrated into the way work gets done.

Addressing the “Assistant Mindset”

A key element of this change management process is helping employees embrace the “assistant mindset.” This means moving away from the idea that they need to do everything themselves and towards a collaborative approach where they leverage Gemini as a powerful ally. It’s about reframing their roles, recognizing that delegating tasks to Gemini can free them to focus on more strategic, creative, and fulfilling aspects of their work.

Mastering the Art of Prompting and Interaction

While generative AI tools like Gemini are incredibly powerful, their effectiveness hinges on our ability to communicate our needs and intentions clearly. This goes beyond simply typing in a text prompt; it’s about mastering the art of interaction and understanding the full range of ways to make the most of Gemini’s capabilities within Google Workspace.

Prompt Engineering: The Basics

Effective prompting is essential for getting the most out of Gemini. Here are a few key principles to keep in mind:

  • Be clear and specific: The more specific your instructions, the better the results. Instead of asking “write a training plan,” try “create a training plan for a new sales onboarding program, including modules on product knowledge, sales techniques, and company values. The plan should be structured for a 3-day program with a mix of presentations, role-playing activities, and online assessments.”
  • Provide context: Give Gemini the information it needs to understand your request. If you want it to summarize a document, provide the document or a link to it.
  • Experiment and iterate: Don’t be afraid to try different prompts and refine your approach based on the results. Prompt engineering is an iterative process.

Beyond Text Prompts: Unlocking Advanced Capabilities

But mastering Gemini goes beyond just text prompts. It’s about understanding how to fully utilize its potential within the Workspace environment. Here’s where things get really interesting:

  • The Gemini Side Panel: This powerful tool allows you to interact with Gemini in more sophisticated ways. You can drag and drop files, incorporate information from multiple sources, and apply Gemini’s analytical capabilities to generate insights and create new content. For example, I recently worked with a client who was impressed with Google Meet’s “take notes for me” and transcript features but found themselves spending significant time reformatting these into structured meeting minutes. I showed them how to use the Gemini Side Panel to combine three files – the meeting agenda, the Google Meet transcript, and an example of minutes from a previous meeting – and then use a prompt to generate new minutes from the transcript, formatted in the same way as the previous minutes. This saved them a huge amount of time and effort.
  • Contextual Awareness: Gemini is designed to understand the context of your work within Workspace. This means it can access and process information from your emails, documents, and other files, allowing for more intelligent and relevant responses.
  • Multimodal Interaction: Gemini is not just about text. It can also process images and potentially other forms of media in the future, opening up even more possibilities for creative expression and problem-solving.

By mastering these advanced interaction techniques, you can unlock the true power of Gemini and transform the way you work.

Looking Ahead: The Future of Work with GenAI

As we’ve seen, generative AI tools like Gemini have the potential to revolutionize the way we work. But the journey has just begun. One of the defining characteristics of this technology is its rapid pace of evolution. New features, capabilities, and ways of interacting with Gemini are emerging constantly. This presents both exciting opportunities and unique challenges for enterprises.

The Need for Continuous Learning

In this dynamic environment, continuous learning is no longer a luxury; it’s a necessity. Employees need to be equipped with the skills and mindset to adapt to new features and functionalities as they become available. Organizations need to foster a culture of continuous learning and provide ongoing support and resources to help their teams stay ahead of the curve.

This rapid evolution was evident in one of our recent pilot programs. At the start of the pilot, Gemini’s ability to analyse quantitative data in the Side Panel within Google Sheets was limited. However, just a few weeks in, Google pushed an update that significantly enhanced these capabilities. This step change opened up a whole new range of possibilities for our pilot participants, but it also highlighted the need for ongoing awareness and education around new features and functionalities.

Human-AI Collaboration: A New Era of Work

The future of work with GenAI is not about humans being replaced by machines; it’s about humans and AI collaborating to achieve more than ever before. This requires a shift in mindset, where we view AI as a partner that can augment our capabilities and free us to focus on higher-value tasks.

Imagine a future where:

  • Marketing teams use GenAI to brainstorm creative campaigns and generate compelling content.
  • Sales teams use GenAI to analyse customer data and personalize their outreach.
  • HR teams use GenAI to streamline recruitment processes and enhance employee engagement.
  • Engineering teams use GenAI to write and debug code, accelerating development cycles.

This collaborative future requires us to develop new skills, embrace new ways of working, and cultivate a mindset of continuous learning.

Responsible AI: Ethical Considerations

As we integrate GenAI into our workflows, it’s essential to consider the ethical implications and ensure responsible use of this powerful technology. This includes:

  • Data privacy and security: Protecting sensitive information and ensuring compliance with data protection regulations.
  • Bias mitigation: Being aware of potential biases in AI models and taking steps to mitigate their impact.
  • Transparency and explainability: Understanding how AI models arrive at their conclusions and ensuring transparency in their use.

This also extends to establishing clear guidelines and policies around the use of GenAI within the organization. In our pilot programs, we’ve been actively helping businesses explore these questions. For instance, in one organization, we facilitated a discussion around the feasibility of having a policy where any content or analysis generated with the assistance of Gemini was clearly noted in the document, presentation, or other output. This type of proactive policy-making can foster transparency, build trust, and ensure that GenAI is used ethically and responsibly.

By embracing these principles, we can harness the transformative power of GenAI while building a future of work that is both innovative and ethical.

Conclusion

The era of generative AI has arrived, and tools like Gemini for Google Workspace are poised to revolutionize the enterprise. But technology alone is not enough. To truly unlock the transformative potential of GenAI, organizations need to invest in change management, foster a culture of continuous learning, and embrace a collaborative approach to human-AI interaction. By doing so, they can empower their employees, enhance productivity, and shape a future of work that is both innovative and ethical.

The journey may be challenging, but the rewards are immense. Let’s embrace this new era of work with open minds, a willingness to learn, and a commitment to responsible AI practices. The future is collaborative, and it’s powered by generative AI.

Source: The Transformative Power of Generative AI: Reimagining the Enterprise with Gemini for Google Workspace

Quickly copy or move existing files in Google Drive from a Google Sheet

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

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.

Source: The Gift of Script: Copy or move file into folder Tool

Google Workspace Developer News: Google Sheets supports smart chips for link previews, Update to Chat API, and more

Chanel Greco is back with Episode 12 of the Google Workspace Developer News. An exciting update is that Google Sheets now supports smart chips for link previews to third-party resources. This now allows developers to create Google Workspace Add-ons that can pull information from their app directly into Sheets.

Other updates include:

  • The Chat API can now create announcement spaces and read/update space permissions.
  • New widgets for card-based Chat app interfaces are generally available.
  • Chat API Space & Membership management with App Authentication is available for Developer Preview Program members.
  • The Chat API can manage custom emoji when enabled for a Workspace organization.
  • The Meet Add-ons SDK can now retrieve the meetingCode property of an ongoing meeting.

More details including links to the documentation are included in the YouTube description.

Source: Google Sheets supports smart chips for link previews, Update to Chat API, and more!

Attention Google Workspace Admins and Developers: New Google Workspace Admin Console API (Policy API) and GAM access

Google recently announced an open beta of the new Policy API. This new API now means developers can read a number of Google Workspace Admin settings programmatically, including those applied at an OU and group level.

This API provides access to the settings that are part of the recommendations published in CISA’s Google Workspace secure configuration baseline, with plans to increase the coverage and functionality of the API.

Additionally, GAM7, the recent merge of GAM and GAM-ADV, has been updated with the ability to read policy data, further streamlining policy management tasks.

You can read more about Audit security settings using the Policy API, now available in open beta and Announcing new policy commands in GAM 7.00.27

Google Apps Script: Mastering version control and deployment with CI/CD

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.

Source: Quick and not so dirty CI/CD solution for Google Apps Script | by Stéphane Giron | Oct, 2024 | Medium

Google Apps Script: The Secret to lightning-fast XML parsing with the Apps Script Engine (ASE)

I increased Apps Script’s performance 10x

Recently, I worked on a project with a client who needed to parse XML files and convert them to JSON. Simple enough, right? The catch? These XML files were massive — each containing hundreds or even thousands of trades. Naturally, the first thing I thought of was Google Apps Script’s built-in XMLService. It’s reliable, widely used, and integrates smoothly into Apps Script. But when I ran it on a file with 1,000 trades, the parsing took 45 seconds! That’s painfully slow for an automation process.

[Editor: In a recent blog post, Dmitry Kostyuk has shared a valuable tip for developers working with Google Apps Script. When faced with the task of parsing large XML files, he discovered that the built-in XMLService was causing significant slowdowns. For files containing thousands of records, the parsing process could take an agonizing 45 seconds.

Dmitry’s solution? Using his Apps Script Engine (ASE) and the xml-js NPM library. By making this switch, he was able to achieve a remarkable 10x increase in parsing speed, reducing the 45-second process to only 4 seconds.

If you’re working with Google Apps Script and large XML files, click the source post link to find out how you can give ASE and xml-js a try!]

Source: Make Apps Script Fast Again! With Apps Script Engine

Enhance your Google Docs skills with these Google Docs API snippets for Google Apps Script

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)
  • Merging table cells
  • Creating and managing footnotes
  • Highlighting superscript numbers in footnotes
  • Resetting borders and shading
  • Creating checkbox bullets
  • Changing section margins
  • Controlling table row overflow
  • Setting custom line and paragraph spacing
  • Replacing placeholders spanning multiple paragraphs
  • Converting between soft and hard line breaks
  • Managing table borders
  • Creating nested lists
  • Retrieving all URLs from a document

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.

Source: Unlocking Power: Leverage the Google Docs API Beyond Apps Script’s Document Service