Write data in Google Sheets through the 'Google Sheets Write Values' step

This function allows you to add data and information to Google Sheets in an intelligent and adaptive way.

Users can specify the spreadsheet URL, the data range, and choose between overwriting existing data or adding as a new row/column. You can use AI models to provide analyses and insights and have all results integrated into Google Sheets, allowing for easy data integration and updating, making the process more efficient and less error-prone.

Defining the Fill Fields to configure the Agent

Field 1 | Spreadsheet for Analysis

The first thing to do is to select your spreadsheet for analysis and data processing!

• As a first step, inside the AI Studio, let's create an advanced step of "App Integration" using the reference sheet's URL and the data reading range. As shown in the screenshot, let's call the step Reader and select "Get Values", because we want to fetch the data.

• Another option is not to configure a fixed URL in the advanced step! For that, just select the option "User Decision", and in this case, it is recommended to add a data input field and a reading range field as shown in the screenshot.

Leading to the selection as below

Field 2 | Handling what will be inserted

Before modifying the spreadsheet, we need to handle and analyze what comes from the spreadsheet and the range of data that was selected, meaning we have to use an assistant that will evaluate and recommend the actions you as a user want to take, right?

Let's bring here an example of research where we want to analyze customer comments and summarize those comments with a single word, like in the example below

Bringing the example we mentioned earlier, let's select the Step AI Assistant and choose the Chat GPT Text Assistant, like in the screenshot below

Notice that inside the assistant, we already have a prompt ready with what we want to execute, meaning I've already guided the ChatGPT assistant on what I want it to do.

Below, you’ll find a table, each cell with a message. I need you to create a new table, without markdown, bringing a single word that summarizes each message. In other words, each new row will have one word. 

Please provide only the table, without markdown, and nothing else. Stick to the format: [["Col1", "Col2"], ["Data1", "Data2"]]

#messages
**reader**

Sure, I need to reference the spreadsheet I inserted. That’s why I’ve highlighted the reference to the **Reader**, the first step I added, which contains the spreadsheet and its data range, just like I’ll bring exactly the data format I want the spreadsheet to provide.

In summary, what have we done so far?

1. We inserted the analysis spreadsheet (Creation of the Reader using the Step App Integration and selecting Get Values, to get the values)

2. We selected the data range for analysis (Creation of the Reader using the Step App Integration)

3. We decided we want to process the data (Creation of the Step for Processing using the AI Assistant)

4. We decided how the processing will be done through a prompt that references the analysis spreadsheet (Definition of the Step for Processing using the AI Assistant)

All good so far?

So, what's still missing?

We already have the reading, the processing... we just need to modify the spreadsheet, right?

Field 3 | Write what we want in the spreadsheet

Now, here comes the easiest part. We've already read and processed, we just need to write.

So, let's create a new advanced step for "App Integration" with the reference spreadsheet URL and the data reading radius. However, unlike field 1, we'll call this step Writer and select "Write Values", because we want to write the data, not retrieve it anymore!

Besides that, I need to make other selections

• Writing range Of course, I’ll need to select which column or space I want the data to be written in. For example, since I have the data in column "E" and that’s what I’m going to work with, I’ll write in column "F," right next to it.

• Data insertion method I can choose to overwrite the selected range data (which is how the selection is shown in the screenshot above) or create new rows to avoid overwriting them.

• Include headers or not Additionally, I can also choose if I want the headers to be considered or not.

• Values of the data to be inserted As the final decision, I need to select what will be inserted into column F (which was my selection). Of course, I want the processed data to be this. And since I already have this "Step" ready, because it was exactly my processing, I just reference it.

Note that everything can also be the user's decision, ok? In this case, since we're doing it based on the spreadsheet, we left everything ready, but feel free to create!

Finalizing the Agent

To finalize the Agent, I just need to finish it with a desired prompt. In this case, since I already have everything I want, I'll keep it as a text-only Agent without the need for applied AI, only returning the comments summarized in one word.

After all, everything is already ready in the AI Steps

Results

Simply put, here is the result after asking to generate

Before

Using the template

After using it

Conclusion

Congrats! You learned how to use the function to add data intelligently and efficiently to your Google Sheets. With this tool, you can:

• Automate data updates: Say goodbye to manual data entry, saving time and avoiding errors.

• Integrate analytics and insights: Use AI models to gain valuable insights and integrate them directly into your spreadsheets.