Parsing a JSON response from Google Sheets

Question

I am working on a Salesforce CRM 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 - Salesforce programming language. 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 what the JSON response 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"
    ]];

Answer 1

You can deserialize the json into an Apex class and then loop through the array of arrays. The result is a List of maps where the keys are the column names and the values are the correlating values.

public class range {
  List<List<String>> values;
}
public static List<Map<String,String>> parseJSON(String jsonStringFromCallout){
  range r = (range)JSON.deserialize(jsonStringFromCallout,range.class);
  List<String> headers = range.values[0];
  List<Map<String,String>> allRows = new List<Map<String,String>>();
  for ( Integer i=1; i<range.values.size(); i++ ){
    Map<String,String> thisRow = new Map<String,String>();
    for ( Integer j=0; j<range.values[i].size(); j++ ){
      thisRow.put(headers[j],range.values[i][j]);
    }
    allRows.add(thisRow);
  }
  return allRows;
}