I am trying to take a research snippet from the web, turn it into a JSON, then put in googlesheets

How To
1
  • I am using a research agent to look for new hotel openings in Jacksonville, Florida. After I get the information, it will be a list of about 8 hotels. I want to put each hotel into a googlesheet with a different row with column headings: hotel name, hotel opening, source of information.
  • I was able to use the research agent to find the information.
    Then I was able to say, take this information and put it into a JSON Array.
    "[
    {
    ““hotel_name””: ““SpringHill Suites Jacksonville Beach Oceanfront””,
    ““opening_date””: "“Opened April 2023"”,
    ““source””: ““TripAdvisor Reviews / Check Prices””
    },
    {
    ““hotel_name””: ““SpringHill Suites Jacksonville Baymeadows””,
    ““opening_date””: "“Opened March 2023"”,
    ““source””: ““TripAdvisor Reviews / Check Prices””
    },
    {
    ““hotel_name””: ““AC Hotel Jacksonville””,
    ““opening_date””: "“Opening March 2024"”,
    ““source””: ““TripAdvisor Reviews / Check Prices””
    },
    {
    ““hotel_name””: ““Tru by Hilton Jacksonville West””,
    ““opening_date””: "“Opening March 2024"”,
    ““source””: ““TripAdvisor Reviews / Check Prices””
    },
    {
    ““hotel_name””: "“Tru by Hilton Jacksonville Airport I-95"”,
    ““opening_date””: "“Opening March 2024"”,
    ““source””: ““TripAdvisor Reviews / Check Prices””
    },
    {
    ““hotel_name””: ““Home2 Suites by Hilton Jacksonville Downtown””,
    ““opening_date””: ““Opening April 28, 2024"”,
    ““source””: ““TripAdvisor Reviews / Check Prices””
    }
    ]”
    That’s what I get. It never seems to be able to be broken out by sections.
    This where I get stuck because I can’t figure out how to map hotel name to hotel name column, opening date to opening date column and source to source column.
    I tried each JSON module but none of them splits it up.

Any ideas?

So I didn’t keep running the agent, I posted the results into a word document and tried to split to from there so I wouldn’t keep using credits.

Include screenshots of

I’m sure it’s probably something simple that I am just not seeing.

2

Welcome to the Make community!

You’ll have to use the “Parse JSON” module to convert your JSON into an array of collections.

Then, you have to use an Iterator module to “Loop” through each array item, to insert into each row.

2 Likes
3

Ok, this is getting much closer but the JSON Parse fail and doesn’t pass to the iterator.
I thought it was going to work!

image
image1232×582 51.3 KB

image
image2110×788 97.2 KB

image
image1974×650 76.2 KB

4

Looks like it’s not valid JSON then. Find another way of parsing data.

Perhaps try a Text Parser module instead

2 Likes
5

OK, I am way out of my league here.

I tried the text parser. I tried each kind and only one sort of worked. it was Match elements.

However, it didn’t parse it. See the below:

image
image453×1204 41.5 KB

image
image2342×785 115 KB

image
image740×714 55.2 KB

image
image691×276 30.3 KB

I felt I just about had it before with the JSON parser, now, nothing is passing forward.

I have to admit, I am totally out of my element and really reliant on you. This is much more complicated than I usually do.

I’m totally stuck.

6

Ok, I started from scratch with the Text Parser, now I am getting the Text Error where I got it before with JSON.

Here is the new flow.
and I will share the step by step.
Again the goal is to take content from a document, organize it by hotel name, hotel opening and then put it into a googlesheet. That’s all I want to do.

Here is the high-level flow:

image
image2384×562 73.2 KB

Here is the messaging the the Assistant in ChatGPT:
Please read the following article Text Content and create an array of objects where each object contains the hotel name, opening date, and source URL. Each hotel’s information should be contained in a separate object with the keys ‘hotelName’, ‘openingDate’, and ‘source’. Ensure the hotel names are accurate, the opening dates are in a clear format (e.g., ‘March 2024’), and the source URLs are valid.

when it runs, this is what the output looks like:

image
image449×1248 42.6 KB

here is the full text of the results:

Here is the requested array of objects based on the provided article:

const hotelData = [
  {
    hotelName: "Airelles Château de Versailles, Le Grand Contrôle",
    openingDate: "March 2024",
    source: "https://www.forbes.com/sites/angelinavillaclarke/2021/02/03/aires-els-grand-controle-new-luxury-hotel-opening-march2024/"
  },
  {
    hotelName: "Base,"      
    openingDate: "May 2023",
    source: "https://www.ignieuws.com/2022/03/16/bob-marleys-son-to-launch-hotel-in-jamaicas-cockpit-country/"
  },
  {
    hotelName: "Conabio de Flores de Villahermosa",
    openingDate: "Late 2022",
    source: "https://www.thesundaily.my/local/tabasco-interview-president-of-new-mexican-ecoresort-sees-malaysia-as-role-model-CI7825341"
  },
  {
    hotelName: "Extravagant Nomad Hotel",
    openingDate: "2026",
    source: "https://www.palmbeachpost.com/story/entertainment/2022/03/10/cirque-du-soleil-founder-meet-the-mad-fairy-hotel/938769400
==
when it comes to the text parser, this is how it's setup:

![image|439x500](upload://87lcp9IZsByjLF79oCTZFJkXR57.png)

This is the recurring error:
![image|664x500](upload://huk11Qo2gTN3CIA1HA8GM4WFk0I.png)

this is the iterator error:
![image|664x500](upload://huk11Qo2gTN3CIA1HA8GM4WFk0I.png)

If anyone has any thoughts? I tried to use ChatGPT to solve this but either as JSON or Text Parser,  they both error out.
----
Thank you in advance. 
I hope this is clear.
7

Make it very clear to gpt only give you a json object and only that not even “here is the requested data”

2 Likes
8

OK. I tried that and still got the same error:

image
image670×834 56.3 KB

image
image674×703 58.7 KB

image
image1748×658 118 KB

This is so frustrating.

9

Kinda have to see the response from gpt to make a judgement here, please copy paste it here so I can take look

11

ok,

Here it is:

image
image684×884 48.7 KB

image
image460×892 34.1 KB

Here’s the copied:

[
    {
        "hotelName": "Four Seasons Hotel Tokyo at Otemachi",
        "openingDate": "September 1, 2020",
        "source": "https://press.fourseasons.com/tokyo/2020-press-releases/fx_dor_024/"
    },
    {
        "hotelName": "Hyatt Regency Tokyo Bay",
        "openingDate": "April 2020",
        "source": "https://www.hyatt.com/ja-JP/hotel/japan/hyatt-regency-tokyo-bay/k1982/news-events"
    },
    {
        "hotelName": "The Ritz-Carlton Nikko",
        "openingDate": "May 2020",
        "source": "https://www.ritzcarlton.com/en/hotels/japan/nikko"
    }
]

==============

I also modified what you shared and moved it to system instructions and tweaked it. that still didn’t work:
image
image435×811 43.4 KB

Indeed, this is a puzzler!

12

Aha, so it gives invalid json hmm, ill show you mine so could replicate it to provide you valid json.

image
image912×1544 133 KB

image
image968×1134 103 KB

I believe this is a GPT version issue! so try with gpt 4 turbo same as me

2 Likes
13

I tweaked the prompt and updated the version, still the same error:

image
image451×1179 76.6 KB

image
image768×742 56.5 KB