Writing to Google Sheets from Perplexity API

How To
, ,
1

Here’s the scenario design - if I can call it that.

Screenshot 2024-08-05 at 5.27.49 PM
Screenshot 2024-08-05 at 5.27.49 PM1777×390 58.5 KB

The scenario is running successfully - here’s a screenshot of the output bundle from perplexity API call.

Screenshot 2024-08-05 at 5.28.46 PM
Screenshot 2024-08-05 at 5.28.46 PM810×751 43.7 KB

The content generated at this stage should be written to a Google spreadsheet.

I have created a Google sheets module (update a row) - with the following fields updated, as shown in the screenshot below:

Screenshot 2024-08-05 at 5.30.40 PM
Screenshot 2024-08-05 at 5.30.40 PM461×512 29 KB

The problem is that - the output generated from Perplexity AI (which is actually generated correctly) is NOT getting written into the Google Sheets tab that I choose. In fact, To the same tab, I am also pulling data from other modules in the same scenario, which are written fine.

I am not able to figure out what the issue is. Please help!

2

Hi Krishna,

From what I can see, the issue looks to be that you are only using one perplexity module. The output of this result is one message and is not seperated. So when you are mapping them into the spreadsheet using the “Updated row” module, it will just be updating each cell in that row with the same message.

You may need to use several perplexity modules, each prompted to complete research for each individual section e.g topic overview, Relevance to target audience etc. and then map these seperate outcomes to the relevant spreadsheet cells.

Hope this helps!

3

Hey @Jamie_Harris

Welcome to make community

Remap the value and try again.

4

Welcome to the Make community!

You are missing the use of aggregators. Please do the Make Academy, as this module’s use is covered there.

Aggregators

Every result (item/record) from an iterator/list/search/match module will output a bundle. This can result in multiple bundles, which then trigger multiple operations in future modules (one operation per bundle). To “combine” multiple bundles into a single variable, you’ll need to use an aggregator of some sort.

Aggregators are modules that accumulate multiple bundles into one single bundle. An example of a commonly-used aggregator module is the Array aggregator module. The next popular aggregator is the Text Aggregator which is very flexible and can apply to many use-cases like building of JSON, CSV, HTML.

There are other types of aggregator modules, click the below links to find out more:

For more information, see “Mapping with Arrays” on how to map an aggregated array below:

Here are some useful links and guides you can use to learn more on how to use the Make platform, apps, and app modules. I found these useful when I was learning Make, and hope they might benefit you too —

Getting Started

Help Centre Basics

Articles & Videos

Partner & Custom Apps

Hope this helps! Let me know if there are any further questions or issues.

— @samliew

5

Um - let me share what I’m stuck with. Perplexity API is correctly generating the output as expected, for all the fields (as verified from the output bundle). I’ve updated the values in the Google sheet correctly and so the writing is happening…

The catch - the most recent value is getting updated in the entire row. Ideally, this is the flow I am expecting:

output 1 from perplexity API module → write to row x, column 1
output 2 from perplexity API module —> write to row x, column 2
output 3 from perplexity API module —> write to row x, column 3
etc.

What’s happening now is:
output 1 from perplexity API module → write to row x, column 1, 2, 3,…
output 2 from perplexity API module —> write to row x, column 1, 2, 3,…
output 3 from perplexity API module —> write to row x, column 1, 2, 3,…

So basically, the most recent output from Perplexity API module is getting written to the entire row; and the entire row is getting replaced with the next output…

Hope that makes sense?

Screenshot 2024-08-05 at 6.11.28 PM
Screenshot 2024-08-05 at 6.11.28 PM573×800 57.2 KB

6

Yes, you’ll need to insert an array aggregator somewhere before your final sheets module, so that you will only be updating a single row.

Hope this helps! Let me know if there are any further questions or issues.

— @samliew

7

Understood - thank you for the quick response. I’m doing it right now.

I’ve added an array aggregator.

Now - in the aggregator module, i have:
Source moddule - i selected "Perplexity AI - chat completion"option
Target structure type is defaulted to custom ( iam not able to change it)
Aggregated fields: There are options for different modules (perpelxity, iterator, tools ,google sheets) and the components inside them.

What do I do here? Apoloigies for asking too many questions - been doing this for hours now! :sweat_smile:

8

Now you select/check all the properties of the modules within the aggregator loop (with a gray background), which should be your Iterator, Perplexity module, Tools.

Run the scenario again, and provide the output bundle of your Aggregator module.

Please provide the output bundles of the modules by running the scenario (or get from the scenario History tab), then click the white speech bubble on the top-right of each module and select “Download input/output bundles”.

A.

Save each bundle contents in your text editor as a bundle.txt file, and upload it here into this discussion thread.

B.

If you are unable to upload files on this forum, alternatively you can paste the formatted bundles in this manner:

Here are two ways to format text so that it won’t be changed by the forum:

A. Type code block manually
Add three backticks ``` before and after the content/bundle, like this:

```
content goes here
```

B. Highlight and click the format button in the editor

Providing the input/output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.

This will allow others to better assist you. Thanks!

— @samliew

9

Sure - here’s the blueprint of the scenario.
blueprint.json (58.4 KB)

The output bundles for perplexity AI module - please note that I’ve set the max tokens to 10 to save tokens during testing:

[
    {
        "id": "da624448-883b-423e-ba22-d8d4ded59925",
        "model": "llama-3-sonar-small-32k-online",
        "created": 1722926648,
        "usage": {
            "prompt_tokens": 163,
            "completion_tokens": 10,
            "total_tokens": 173
        },
        "object": "chat.completion",
        "choices": [
            {
                "index": 0,
                "finish_reason": "length",
                "message": {
                    "role": "assistant",
                    "content": "To cook the tastiest potato curry, start by"
                },
                "delta": {
                    "role": "assistant",
                    "content": ""
                }
            }
        ]
    }
]

[
    {
        "id": "29adef32-6334-49cd-a46f-3aa86761d516",
        "model": "llama-3-sonar-small-32k-online",
        "created": 1722926652,
        "usage": {
            "prompt_tokens": 191,
            "completion_tokens": 10,
            "total_tokens": 201
        },
        "object": "chat.completion",
        "choices": [
            {
                "index": 0,
                "finish_reason": "length",
                "message": {
                    "role": "assistant",
                    "content": "Recent developments in cooking the tastiest potato curry include"
                },
                "delta": {
                    "role": "assistant",
                    "content": ""
                }
            }
        ]
    }
]

There are 4 such output bundles - all similar (output from the perplexity API, after running prompts through an iterator). I am not able download all outbut bundles at once, for some reason.

Ive been trying several variations - from what I understand, it seems that the output is fine, from the pereplxity ai module - I am not sure how this output is being stored. I tried using an aggregator, but the problem still remains - how to “extract” the right content output from the array, and map it to a variable/write to a cell in a spreadsheet.