+ Start a Discussion
Kamil MieczakowskiKamil Mieczakowski 

Parsing JSON response from Google Sheets

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? 
 
SalesFORCE_enFORCErSalesFORCE_enFORCEr
I would suggest to create a List<String> just for the headers and rest for the values. Then, iterate over the values and create a Map<String,String>. Something like this:
 new Map<String,String>{headerList[1]=>mapVal[1] ,headerList[2]=>mapVal[2])
Alain CabonAlain Cabon
Hi,

Did you try https://json2apex.herokuapp.com/ ?

User-added image
Just click on the button 'Create Apex'
 
//
// Generated by JSON2Apex http://json2apex.herokuapp.com/
//

public class JSON2Apex {

	public class JSON2Apex {
		public String range;
		public String majorDimension;
		public List<List<String>> values;
	}
	
	public static JSON2Apex parse(String json) {
		return (JSON2Apex) System.JSON.deserialize(json, JSON2Apex.class);
	}
}

You will get also the class of test ( ... what more could you ask for? )

The first list of String (in the list of list) contains the header values and the next list the values.

Regards

Alain
Kamil MieczakowskiKamil Mieczakowski
Thank you for your replies guys. This tool looks amazing!

Do you mind me asking how would you process say 2500 rows of data using this method?

Ideally I would want to create multiple JSON files (1 key value pair per row), to then iterate through each of them to capture the ones that meet particular conditions.
Alain CabonAlain Cabon
You have the governor limits as usual.

The first limit reached could be the CPU time limit: 

https://developer.salesforce.com/forums/?id=906F0000000Bb3GIAS

... Daniel Ballinger, many good answers for tricky technical qestions.

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm

Regards
Alain
SalesFORCE_enFORCErSalesFORCE_enFORCEr
I am doing that in a batch. 200 records at a time.