[Organizational AI Utilization #212] I've clarified the Google Workspace Studio spreadsheet integration, as the original expression was a bit confusing.

Hello! This is Terada.

Currently, I serve as the Representative Director of AI Digital Community (ADC), a community for AI practitioners such as AI promoters at digital-related companies, as well as the Representative Director of FURIKAKE Partners Inc., which supports the "xAI" transformation of client businesses and AI Portalize Inc., which provides products to support organizational AI utilization, supporting organizational AI utilization from various perspectives!

Google Workspace Studio is a tool that allows you to automate business workflows without coding by combining Gemini with Workspace apps like Spreadsheets and Gmail.

Click here for an article on the overview of Google Workspace.

Workflows are built by stacking "start conditions (triggers)" and "actions."

However, when trying to integrate spreadsheets, the names of the configuration items, such as "Search for rows to monitor for changes" and "Set new values for each column," are not intuitively clear, and I get the impression that many people get stuck here.

This time, I have organized the four configuration items that appear in spreadsheet integration by dividing them into the "input (trigger) side" and "output (write) side," with concrete examples.

Spreadsheets are easy to use in two scenarios: "input" and "output"

Workflows consist of "start conditions (when to run)" and "actions (what to do)." Spreadsheets appear in both of these.

• Input side (start condition): Detect changes in the sheet to start the workflow (e.g., when a sheet is changed)

• Output side (action): Write the workflow processing results to the sheet (e.g., update a row)

The meaning of the configuration items differs between the input side and the output side. If you confuse these, you are likely to stumble in the settings, so let's look at them in order.

Note that as a common setting, in both cases, you first specify the target file in the "Spreadsheet" field and the target sheet (tab) in the "Sheet" field.

Also, all column specifications are done using the column names (headers) entered in the first row of the sheet. (You can manage to do it otherwise through settings, but it's not worth the trouble, so this method is recommended.) This is why the note "Name of the first row" is displayed in the setting field.

Please make sure to enter column names like "Company Name" and "Domain" in the first row.

Trigger side setting 1: "Search for rows to monitor for changes"

The setting for the start condition "When a sheet is changed" determines "when and with what data to start" the workflow. There are two main configuration items.

The first setting, 'Search for rows to monitor for changes,' is for narrowing down the target, specifically 'which rows should be checked in the first place'.

Instead of watching for changes in every row when there is a lot of data in the sheet, you only monitor rows that meet specific conditions.

• Monitor only rows where the 'Person in Charge' column is 'Me'

• Monitor only rows where the 'Status' column is 'Unprocessed'

• If you want to target all rows, set the value to 'All'

Trigger-side setting 2: 'Select new value to monitor'

The second setting, 'Select new value to monitor,' is the trigger configuration that determines 'what value change in the monitored row should cause the flow to run'.

It detects the moment a specific column in the monitored row is rewritten to a specified value and starts the flow.

• Run the flow when the value in the 'Priority' column is rewritten to 'High'

• Run the flow when the value in the 'Execution Status' column is rewritten to 'OK'

In other words, it is a two-stage configuration where you narrow down the target rows with 'Search for rows to monitor for changes' and determine the moment of firing with 'Select new value to monitor'.

I think it becomes easier to understand if you rephrase it as: 'When the value of which column in which row changes to what'.

Write-side setting 1: 'Search for rows to update'

Next is the output side. The 'Update row' action determines 'where in the sheet to write what' during the flow. This also has two setting items.

The first setting, 'Search for rows to update,' is for specifying the destination, specifically 'which row do you want to overwrite'.

You specify conditions to find the specific row you want to overwrite or append to from among the many rows in the sheet.

• Find the row where the 'Employee ID' column is '1234'

• Find the same row that triggered the start condition

Write-side setting 2: 'Set new values for each column'

The second item, 'Set new values for each column,' is where you specify exactly which column in the found row you want to write new data to, and what that data should be.

For the values to be written, you can choose not only fixed text but also data (variables) generated in previous steps. This is the core part of AI integration.

• Overwrite the 'Status' column of the found row with the text 'Completed'

• Write the response generated by the AI (Gem) in the previous step into the 'Research Results' column of the found row

Common pitfalls

Finally, I have summarized some common pitfalls to watch out for when building your own flows.

• Columns are specified using the header name in the first row. If the first row is empty or if you change column names after creating the flow, the conditions may not work as intended.

• If you overwrite the same column used as a trigger in 'Update Row,' that change might be detected as a new trigger, potentially leading to unintended continuous execution. It is safer to design separate columns for triggers and for writing data.

• Trigger values are evaluated strictly based on their exact spelling. Variations like 'OK' vs 'ok', or full-width vs half-width characters, may cause them to go undetected, so please standardize the input values as a rule.

• Once you have created a flow, it is safer to run a 'Test Run' to check the results before turning it 'On' for production.

When in doubt, think in terms of 'which row,' 'which column,' and 'what value'

Spreadsheet integration settings can be understood by focusing on four points: on the input side, 'Search for rows to monitor changes' (which row to watch) and 'Select new values to monitor' (what change triggers the action); and on the output side, 'Search for rows to update' (which row to write to) and 'Set new values for each column' (which column to write to and what to write).

If you get lost in the settings screen, try thinking about whether you are being asked for 'which row,' 'which column,' or 'what value.'

There is no need to be confused by the unclear item names. I hope you will try it out starting with a simple flow!

Thank you for reading!
Click here for past AI-related articles!

By the way, I have also published a book summarizing AI promotion within organizations. Please take a look if you are interested.