Get all the data from API -> Excel/ Google Sheets

Questions & Answers
, ,
1

I am trying to get all the data from API → Excel/ Google Sheets.

The setup should be;

  1. Google Sheet that shows api what to search for (query everything in row A)
  2. HTTP API call to execute the query
  3. Create, or update a Google Sheet/ Excel

Right now I have two problems with step 3.
a) If I set it to “Update row” it forces me to map the JSON data structure (which is faulty and seems impossible to do with Make as it is with arrays and inside arrays).
b) When I set it to create a new Excel/ Google Sheet it just creates it but does not input any data into it

Please help :pray: spent 15+ hours on this, tried numerous ways and numerous software to do this. I also think SFTP connection could solve this somehow.

CleanShot 2024-01-12 at 10.31.13@2x
CleanShot 2024-01-12 at 10.31.13@2x1742×626 48.2 KB

CleanShot 2024-01-12 at 10.32.14@2x
CleanShot 2024-01-12 at 10.32.14@2x844×484 69 KB

2

Welcome to the Make community!

Please provide the output bundles of the modules by running the scenario, then click the white speech bubble on the top-right of each module and select “Download output bundles”.
Screenshot_2023-10-06_141025

A.

Save the bundle contents in your text editor as a bundle.txt file, and upload it here into this discussion thread.
Uploading it here will look like this:

bundle.txt (12.3 KB)

B.

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

  • Either add three backticks ``` before and after the code, like this:

    ```
    input/output bundle content goes here
    ```

  • Or use the format code button in the editor:
    Screenshot_2023-10-02_191027

Providing the 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!

2 Likes
3

Thank you! Here it is.

[
    {
        "statusCode": 200,
        "headers": [
            {
                "name": "date",
                "value": "Fri, 12 Jan 2024 11:57:23 GMT"
            },
            {
                "name": "content-type",
                "value": "application/json; charset=utf-8"
            },
            {
                "name": "content-length",
                "value": "3232"
            },
            {
                "name": "connection",
                "value": "close"
            },
            {
                "name": "etag",
                "value": "W/\"ca0-nfEhbWOCErPoCfbcCnzs/xl8/Hc\""
            },
            {
                "name": "x-powered-by",
                "value": "Express"
            },
            {
                "name": "x-ratelimit-requests-limit",
                "value": "50"
            },
            {
                "name": "x-ratelimit-requests-remaining",
                "value": "39"
            },
            {
                "name": "x-ratelimit-requests-reset",
                "value": "2587471"
            },
            {
                "name": "x-ratelimit-rapid-free-plans-hard-limit-limit",
                "value": "500000"
            },
            {
                "name": "x-ratelimit-rapid-free-plans-hard-limit-remaining",
                "value": "499990"
            },
            {
                "name": "x-ratelimit-rapid-free-plans-hard-limit-reset",
                "value": "2587471"
            },
            {
                "name": "server",
                "value": "RapidAPI-1.2.8"
            },
            {
                "name": "x-rapidapi-version",
                "value": "1.2.8"
            },
            {
                "name": "x-rapidapi-region",
                "value": "AWS - eu-west-1"
            }
        ],
        "cookieHeaders": [],
        "data": "{\"graph\":{\"@context\":\"http://schema.org\",\"@graph\":[{\"@type\":\"Person\",\"address\":{\"@type\":\"PostalAddress\",\"addressLocality\":\"Prague, Czechia\",\"addressCountry\":\"CZ\"},\"alumniOf\":[{\"@type\":\"EducationalOrganization\",\"name\":\"Vysoká škola Roborecruiterská\",\"member\":{\"@type\":\"OrganizationRole\",\"startDate\":2016,\"endDate\":2016}}],\"awards\":[],\"image\":{\"@type\":\"ImageObject\",\"contentUrl\":\"https://media.licdn.com/dms/image/C5603AQE2D5AnzEVQLg/profile-displayphoto-shrink_200_200/0/1547211162337?e=2147483647&v=beta&t=eVz4JxckIItSDqNqa1deHTg6YBkcJjfu_2l0W5T8Wnc\"},\"jobTitle\":[\"Digital Recruitment Guru\"],\"name\":\"Bibi Behaverová\",\"sameAs\":\"https://cz.linkedin.com/in/bibi-behaverova\",\"url\":\"https://cz.linkedin.com/in/bibi-behaverova\",\"memberOf\":[],\"worksFor\":[{\"@type\":\"Organization\",\"name\":\"Behavera\",\"url\":\"https://cz.linkedin.com/company/behavera\",\"member\":{\"@type\":\"OrganizationRole\",\"startDate\":\"2016-01\"}}],\"knowsLanguage\":[],\"disambiguatingDescription\":\"\",\"interactionStatistic\":{\"@type\":\"InteractionCounter\",\"interactionType\":\"https://schema.org/FollowAction\",\"name\":\"Follows\",\"userInteractionCount\":179},\"description\":\"Jsem vaše osobní digitální recruiterka. Umím rozeznat lidi, které váš byznys potřebuje. Používám k tomu simulační hry a behaviorální měření. Udělám za vás výběrové řízení, ověřím dovednosti uchazečů a doporučí vám ty nejlepší. Vy už je jen pozvete na pohovor. Přijďte si se mnou popovídat na www.bibi.behavera.com.\"},{\"@type\":\"WebPage\",\"url\":\"https://cz.linkedin.com/in/bibi-behaverova\",\"reviewedBy\":{\"@type\":\"Person\",\"name\":\"Bibi Behaverová\"}}]},\"extractor\":{\"education\":[{\"name\":\"Vysoká škola Roborecruiterská\",\"degree\":\"HRE. (HR Evangelistka)\",\"field\":\"Robotika a psychometrika v aplikovaném HR\",\"activities\":null,\"startDate\":\"2016\",\"endDate\":\"2016\"}],\"address\":{\"@type\":\"PostalAddress\",\"addressLocality\":\"Prague, Czechia\",\"addressCountry\":\"CZ\"},\"alumniOf\":[{\"@type\":\"EducationalOrganization\",\"name\":\"Vysoká škola Roborecruiterská\",\"member\":{\"@type\":\"OrganizationRole\",\"startDate\":2016,\"endDate\":2016}}],\"awards\":[],\"image\":{\"@type\":\"ImageObject\",\"contentUrl\":\"https://media.licdn.com/dms/image/C5603AQE2D5AnzEVQLg/profile-displayphoto-shrink_200_200/0/1547211162337?e=2147483647&v=beta&t=eVz4JxckIItSDqNqa1deHTg6YBkcJjfu_2l0W5T8Wnc\"},\"jobTitle\":[\"Digital Recruitment Guru\"],\"name\":\"Bibi Behaverová\",\"sameAs\":\"https://cz.linkedin.com/in/bibi-behaverova\",\"url\":\"https://cz.linkedin.com/in/bibi-behaverova\",\"memberOf\":[],\"worksFor\":[{\"@type\":\"Organization\",\"name\":\"Behavera\",\"url\":\"https://cz.linkedin.com/company/behavera\",\"member\":{\"@type\":\"OrganizationRole\",\"startDate\":\"2016-01\"}}],\"knowsLanguage\":[],\"disambiguatingDescription\":\"\",\"interactionStatistic\":{\"@type\":\"InteractionCounter\",\"interactionType\":\"https://schema.org/FollowAction\",\"name\":\"Follows\",\"userInteractionCount\":179},\"description\":\"Jsem vaše osobní digitální recruiterka. Umím rozeznat lidi, které váš byznys potřebuje. Používám k tomu simulační hry a behaviorální měření. Udělám za vás výběrové řízení, ověřím dovednosti uchazečů a doporučí vám ty nejlepší. Vy už je jen pozvete na pohovor. Přijďte si se mnou popovídat na www.bibi.behavera.com.\"}}",
        "fileSize": 3232
    }
]```
4

Hmm, that output bundle doesn’t look parsed. Could you try to Open up your HTTP module settings and Switch on “Parse Response” to “YES”?

Screenshot_2024-01-12_220143

Then, provide your new output bundle again.

2 Likes
5 
[
    {
        "statusCode": 200,
        "headers": [
            {
                "name": "date",
                "value": "Fri, 12 Jan 2024 15:09:24 GMT"
            },
            {
                "name": "content-type",
                "value": "application/json; charset=utf-8"
            },
            {
                "name": "content-length",
                "value": "3232"
            },
            {
                "name": "connection",
                "value": "close"
            },
            {
                "name": "etag",
                "value": "W/\"ca0-nfEhbWOCErPoCfbcCnzs/xl8/Hc\""
            },
            {
                "name": "x-powered-by",
                "value": "Express"
            },
            {
                "name": "x-ratelimit-requests-limit",
                "value": "50"
            },
            {
                "name": "x-ratelimit-requests-remaining",
                "value": "38"
            },
            {
                "name": "x-ratelimit-requests-reset",
                "value": "2575950"
            },
            {
                "name": "x-ratelimit-rapid-free-plans-hard-limit-limit",
                "value": "500000"
            },
            {
                "name": "x-ratelimit-rapid-free-plans-hard-limit-remaining",
                "value": "499989"
            },
            {
                "name": "x-ratelimit-rapid-free-plans-hard-limit-reset",
                "value": "2575950"
            },
            {
                "name": "server",
                "value": "RapidAPI-1.2.8"
            },
            {
                "name": "x-rapidapi-version",
                "value": "1.2.8"
            },
            {
                "name": "x-rapidapi-region",
                "value": "AWS - eu-west-1"
            }
        ],
        "cookieHeaders": [],
        "data": {
            "graph": {
                "@context": "http://schema.org",
                "@graph": [
                    {
                        "@type": "Person",
                        "address": {
                            "@type": "PostalAddress",
                            "addressLocality": "Prague, Czechia",
                            "addressCountry": "CZ"
                        },
                        "alumniOf": [
                            {
                                "@type": "EducationalOrganization",
                                "name": "Vysoká škola Roborecruiterská",
                                "member": {
                                    "@type": "OrganizationRole",
                                    "startDate": 2016,
                                    "endDate": 2016
                                }
                            }
                        ],
                        "awards": [],
                        "image": {
                            "@type": "ImageObject",
                            "contentUrl": "https://media.licdn.com/dms/image/C5603AQE2D5AnzEVQLg/profile-displayphoto-shrink_200_200/0/1547211162337?e=2147483647&v=beta&t=eVz4JxckIItSDqNqa1deHTg6YBkcJjfu_2l0W5T8Wnc"
                        },
                        "jobTitle": [
                            "Digital Recruitment Guru"
                        ],
                        "name": "Bibi Behaverová",
                        "sameAs": "https://cz.linkedin.com/in/bibi-behaverova",
                        "url": "https://cz.linkedin.com/in/bibi-behaverova",
                        "memberOf": [],
                        "worksFor": [
                            {
                                "@type": "Organization",
                                "name": "Behavera",
                                "url": "https://cz.linkedin.com/company/behavera",
                                "member": {
                                    "@type": "OrganizationRole",
                                    "startDate": "2016-01"
                                }
                            }
                        ],
                        "knowsLanguage": [],
                        "disambiguatingDescription": "",
                        "interactionStatistic": {
                            "@type": "InteractionCounter",
                            "interactionType": "https://schema.org/FollowAction",
                            "name": "Follows",
                            "userInteractionCount": 179
                        },
                        "description": "Jsem vaše osobní digitální recruiterka. Umím rozeznat lidi, které váš byznys potřebuje. Používám k tomu simulační hry a behaviorální měření. Udělám za vás výběrové řízení, ověřím dovednosti uchazečů a doporučí vám ty nejlepší. Vy už je jen pozvete na pohovor. Přijďte si se mnou popovídat na www.bibi.behavera.com."
                    },
                    {
                        "@type": "WebPage",
                        "url": "https://cz.linkedin.com/in/bibi-behaverova",
                        "reviewedBy": {
                            "@type": "Person",
                            "name": "Bibi Behaverová"
                        }
                    }
                ]
            },
            "extractor": {
                "education": [
                    {
                        "name": "Vysoká škola Roborecruiterská",
                        "degree": "HRE. (HR Evangelistka)",
                        "field": "Robotika a psychometrika v aplikovaném HR",
                        "activities": null,
                        "startDate": "2016",
                        "endDate": "2016"
                    }
                ],
                "address": {
                    "@type": "PostalAddress",
                    "addressLocality": "Prague, Czechia",
                    "addressCountry": "CZ"
                },
                "alumniOf": [
                    {
                        "@type": "EducationalOrganization",
                        "name": "Vysoká škola Roborecruiterská",
                        "member": {
                            "@type": "OrganizationRole",
                            "startDate": 2016,
                            "endDate": 2016
                        }
                    }
                ],
                "awards": [],
                "image": {
                    "@type": "ImageObject",
                    "contentUrl": "https://media.licdn.com/dms/image/C5603AQE2D5AnzEVQLg/profile-displayphoto-shrink_200_200/0/1547211162337?e=2147483647&v=beta&t=eVz4JxckIItSDqNqa1deHTg6YBkcJjfu_2l0W5T8Wnc"
                },
                "jobTitle": [
                    "Digital Recruitment Guru"
                ],
                "name": "Bibi Behaverová",
                "sameAs": "https://cz.linkedin.com/in/bibi-behaverova",
                "url": "https://cz.linkedin.com/in/bibi-behaverova",
                "memberOf": [],
                "worksFor": [
                    {
                        "@type": "Organization",
                        "name": "Behavera",
                        "url": "https://cz.linkedin.com/company/behavera",
                        "member": {
                            "@type": "OrganizationRole",
                            "startDate": "2016-01"
                        }
                    }
                ],
                "knowsLanguage": [],
                "disambiguatingDescription": "",
                "interactionStatistic": {
                    "@type": "InteractionCounter",
                    "interactionType": "https://schema.org/FollowAction",
                    "name": "Follows",
                    "userInteractionCount": 179
                },
                "description": "Jsem vaše osobní digitální recruiterka. Umím rozeznat lidi, které váš byznys potřebuje. Používám k tomu simulační hry a behaviorální měření. Udělám za vás výběrové řízení, ověřím dovednosti uchazečů a doporučí vám ty nejlepší. Vy už je jen pozvete na pohovor. Přijďte si se mnou popovídat na www.bibi.behavera.com."
            }
        },
        "fileSize": 3232
    }
]
6

Looks like you are able to start mapping the fields into the Google Sheet.

Take a screenshot of the Google Sheets “Update a Row” module if you need further assistance.

2 Likes
7

CleanShot 2024-01-12 at 16.28.58@2x
CleanShot 2024-01-12 at 16.28.58@2x928×1034 93.9 KB

CleanShot 2024-01-12 at 16.29.40@2x
CleanShot 2024-01-12 at 16.29.40@2x614×1060 81 KB

Not quite
a) there is an error for a few arrays
b) the JSON mapping here isn’t correct - doesn’t show all the data (for some subset of data, for others no data just code snippets)

How can I do this without mapping it out? I just want all the information in the sheet from the query