Help with Perplexity output into Google Sheet

Hello everyone,

this is my first automation in Make.com. I thought I had figured it out, but I’ve run into some issues. I’m hoping someone here can help me!

I’m creating an assistant to perform company research.

Here’s the workflow:

  1. Check for new rows in Google Sheet.
  2. Perplexity takes Company Name from Google Sheet and looks for other specified information.
  3. Update the Google Sheet with this information in designated columns.

I realized I need to transform the text data from Perplexity into JSON format so it can be mapped correctly into the Google Sheet

What I tried to do:

  1. I asked Perplexity to output data in JSON format so I could use the Parse JSON module. However, I received an error (* The operation failed with an error. Source is not valid JSON.)

  2. I tried using the OpenAI module to convert text into structured data, and it seemed to be working, but then I got an error: “[400] Invalid ‘functions[0].description’: string too long. Expected a string with a maximum length of 1024, but got a string with a length of 1056.” I was testing on a smaller batch of data, so I’m guessing this approach won’t work on a larger scale.

  3. I also attempted to use the Transform to JSON module, followed by the Parse JSON module, but got another error: “The operation failed with an error. Root value of JSON structure must be an object or array.”

I’ve spent several hours trying to resolve these issues, and I’m feeling stuck. Any suggestions or guidance would be greatly appreciated!

Thank you in advance!

Hello @ranik, please share scenario screenshots or the blueprint.

I request such because there’s a few differing suggestions in my mind and I don’t know what’s most relevant to you.

Hello @ImMichaelCannon!

Below you can find screenshots of all three versions I’ve tried. Instead, “Watch new rows” module, I’ve put “Search Rows” as for now there are no new rows.



Thanks!

What is the output from perplexity that you want to map? How do you want the data to look like in google sheets?

@LinkYourTech

Prompt for Perplexity

"Provide detailed information about the company named “{{1.1}}”:

  • Industry: [Use specific terms like Consulting, Retail, SaaS]
  • Location: [Only provide City, Country]
  • Number of Employees: [Exact number]
  • Year Founded: [Year]
  • Key Products or Services: [List no more than 3 main products or services]
  • Short Description: [A very brief description, no more than 10 words]

Keep the response short and formatted exactly as shown above, with no additional explanations."

The output in plain text:


The output if I ask for JSON:

I want the data to be mapped to designated columns.

The prompt asking for JSON should work in the parse JSON module. You just have to get rid of the '“json and the '”. You can use a replace function to delete those text but issue is that the AI might format it differently the next time.

You need to make sure the structure is outputted consistently each time. OpenAI has the structured data module. Perhaps use that instead.

Hope this helps!

See Issue with JSON Parsing in OpenAI Response - #2 by samliew for removal of the markdown code fence.

1 Like

You might consider stating you want JSON output and adding example output within in the prompt. When I require results consistency, I’ll use the CO-STAR framework with example output.

1 Like

Thanks! Unfortunately, OpenAI doesn’t perform online research that is crucial in this workflow.

The replace function worked! Thanks

As a new Make user it wasn’t so obvious what I need to do with that information :sweat_smile:

For others - just paste the replace in JSON string

example

Good structure is always good idea, however Perplexity insisted in providing this format with ```json in the begining… But still - thank you for your reply!

1 Like

The replace is a hit and miss with Perplexity. I couldn’t get it to work all the time. After removing the ```json it comes out with an invalid JSON error. Instead of tearing off my hair, I used another module to get the data I want so I don’t have to use JSON to separate them.

Which module did you use?

After few days, I’ve encountered invalid JSON error, as the Perplexity added “```” without json. So I added another replace function so now it looks like this:

json