You need to sign in to do that
Don't have an account?
Kamil 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:
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?
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?
new Map<String,String>{headerList[1]=>mapVal[1] ,headerList[2]=>mapVal[2])
Did you try https://json2apex.herokuapp.com/ ?
Just click on the button 'Create Apex'
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
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.
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