1

I am working on a project where I need to access values in a Google Sheet, scan them for certain keywords, and if they meet a particular condition, copy the data from a given row into an object in Salesforce.

I am accessing a body of a Google Sheet using Google Sheets API and Apex.

The problem that I am having is that each data row that I am getting from the Google Sheets file is a separate JSON file.

As you will see in the example below the keys are only located in the first JSON file, then each file that follows contains only values.

Is there a way to pair each JSON file that contains values (from 2nd one onwards) with the keys in the first file?

Here's how the response body looks like:

    "range": "Angels!B2:AD2501",
  "majorDimension": "ROWS",
  "values": [
    [
      "Complete?",
      "Name",
      "ID :",
      "Source",
      "LinkedIn",
      "Twitter",
      "Profile",
      "",
      "AA Profile",
      "Email",
      "Location: City",
      "Location: Country",
      "Twitter Bio",
      "Bio",
      "Known For:",
      "Investments",
      "Preferred Industry",
      "Vertical",
      "Associated Venture Fund",
      "Type",
      "Total Investments",
      "Total Exits",
      "",
      "Priority",
      "Comments",
      "Email",
      "Contact Owner",
      "Account Owner",
      "In CRM"
    ],
    [
      "Yes",
      "John Doe",
      "2305",
      "CrowdSourced",
      "https://www.linkedin.com/in/someone-34738265",
      "",
      "",
      "",
      "https://angel.co/person",
      "",
      "Something",
      "UK",
      "",
      "Executive Manager",
      "Long term investor.",
      "list, of, companies, separated,by, a, comma",
      "IT, Advertising",
      "",
      "",
      "Person (individual)",
      "239",
      "16",
      "TRUE",
      "H"
    ],
    [
      "Yes",
      "A. Nikiforov",
      "766",
      "Pitchbook2",
      "https://www.linkedin.com/pub/dir/alexey/nikiforov",
      "",
      "https://my.pitchbook.com?i=106763-86",
      "",
      "",
      "gfm@polytechnics.spb.ru",
      "Saint Petersburg",
      "Russia",
      "",
      "Mr. A. Nikiforov is the Owner at Izdatelstvo Politekhnika. Mr. A. Nikiforov is the Owner at A. Nikiforov.",
      " ",
      "Izdatelstvo Politekhnika",
      "Media",
      "",
      "",
      "Angel (individual)",
      "1",
      "",
      "FALSE"
    ],
    [
      "Yes",
      "Aarish Patel",
      "1043",
      "Pitchbook2",
      "https://www.linkedin.com/in/aarish-patel-06387983",
      "",
      "https://my.pitchbook.com?i=151254-01",
      "",
      "",
      "",
      "",
      "",
      "",
      "Mr. Patel serves as the Non-Executive Director at Reds True Barbecue. He serves as the Angel Investor at Aarish Patel.",
      " ",
      "Reds True Barbecue",
      "Restaurants, Hotels and Leisure, Retail",
      "",
      "",
      "Angel (individual)",
      "1",
      "",
      "FALSE"
    ]];

As you can see in the example the keys are only located in the first JSON file, then each JSON file that follows contains values.

Is there a way to pair each JSON file that contains values (from 2nd one onwards) with the keys in the first file?

Improve this question
6
  • Have you built your Object yet from this response? You will need to get creative with your Maps, and how you key them
    – EricSSH
    Jun 15, 2017 at 22:31
  • No I haven't, still thinking how best to tackle this. There are 2500 rows to deal with so there's also issue of limits etc.
    – Kamil Mieczakowski
    Jun 15, 2017 at 22:31
  • Can you post your JSON?
    – EricSSH
    Jun 15, 2017 at 22:32
  • Oh I just re-read your post, I'm not sure how that object would look
    – EricSSH
    Jun 15, 2017 at 22:35
  • 1
    What do you need to do with this data? Looking at the JSON, it's an array, so the first index would be your headers, so that can you be your map keys or class properties, though some of the keys are empty, so that would need to be addressed.
    – Andrew L.
    Jun 16, 2017 at 0:02

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.