Parse Json - Google Apps Script

Question

我正在尝试从谷歌脚本中的网络钩子收集 JSON 响应并将结果输出到谷歌表格中。

理想情况下，我想解析 JSON 数据并将其输出到电子表格中的列中，(每次触发 webhook 时，此数据都会在电子表格的下一行输入(

以下是从 Webhook 收集的示例数据：

{parameter={environment=prod, payload={"TEST":"WARNING: THIS IS A TEST PAYLOAD ONLY. THESE IDs ARE NOT VALID FOR THIS RETAILER","id":"b53744ce-2d21-11e2-8057-080027706aa2","retailer_id":"9a5521c3-2d20-11e2-8057-080027706aa2","customer_code":"JOEB","balance":"0.000","points":0,"note":"<p>This is a <em>really</em> nice customer</p>","year_to_date":"6750.00000","sex":"M","date_of_birth":"1986-10-12","custom_field_1":"foo","custom_field_2":"bar","custom_field_3":"baz","custom_field_4":"","updated_at":"2012-11-14 17:47:57","created_at":"2012-11-13 12:35:57","contact":{"first_name":"Joe","last_name":"Bloggs","company_name":"Acme Limited","phone":"021 555 55555","mobile":"","fax":"","email":"[e-mail]","twitter":"@joebloggs","website":"http://www.example.com/","physical_address1":"12 Jimmy Street","physical_address2":"","physical_suburb":"Johnsonville","physical_city":"Jamestown","physical_postcode":"65225","physical_state":"WA","physical_country_id":"AU","postal_address1":"12 Jimmy Street","postal_address2":"","postal_suburb":"Johnsonville","postal_city":"Jamestown","postal_postcode":"65225","postal_state":"WA","postal_country_id":"AU"},"contact_first_name":"Joe","contact_last_name":"Bloggs"}, retailer_id=b8ca3a6d-ea18-11e4-ee41-1ca4a74ff9da, type=customer.update, domain_prefix=123}, contextPath=, contentLength=1859, queryString=null, parameters={environment=[Ljava.lang.Object;@53432cae, payload=[Ljava.lang.Object;@2af01d0d, retailer_id=[Ljava.lang.Object;@4282d52a, type=[Ljava.lang.Object;@5a178fb1, domain_prefix=[Ljava.lang.Object;@107bfe01}, postData=FileUpload}

这是我现有的谷歌脚本代码，它只是将json数据转储到下一行

function doPost(e) {
return handleResponse(e);
}
function handleResponse(e) {
var ss =SpreadsheetApp.openById('XYZ');
var lastRow = sheet.getLastRow() + 1;
SpreadsheetApp.getActiveSheet().getRange('a' + lastRow).setValue(e);  
}

我已经尝试过JSON.parse，但似乎没有很快。

非常感谢

Answer 1

并非所有内容都是JSON格式。 你可以在这里查看。 下面我分离了我认为是 JSON 格式的数据。

{
"TEST": "WARNING: THIS IS A TEST PAYLOAD ONLY. THESE IDs ARE NOT VALID FOR THIS RETAILER",
"id": "b53744ce-2d21-11e2-8057-080027706aa2",
"retailer_id": "9a5521c3-2d20-11e2-8057-080027706aa2",
"customer_code": "JOEB",
"balance": "0.000",
"points": 0,
"note": "<p>This is a <em>really</em> nice customer</p>",
"year_to_date": "6750.00000",
"sex": "M",
"date_of_birth": "1986-10-12",
"custom_field_1": "foo",
"custom_field_2": "bar",
"custom_field_3": "baz",
"custom_field_4": "",
"updated_at": "2012-11-14 17:47:57",
"created_at": "2012-11-13 12:35:57",
"contact": {
"first_name": "Joe",
"last_name": "Bloggs",
"company_name": "Acme Limited",
"phone": "021 555 55555",
"mobile": "",
"fax": "",
"email": "[e-mail]",
"twitter": "@joebloggs",
"website": "http://www.example.com/",
"physical_address1": "12 Jimmy Street",
"physical_address2": "",
"physical_suburb": "Johnsonville",
"physical_city": "Jamestown",
"physical_postcode": "65225",
"physical_state": "WA",
"physical_country_id": "AU",
"postal_address1": "12 Jimmy Street",
"postal_address2": "",
"postal_suburb": "Johnsonville",
"postal_city": "Jamestown",
"postal_postcode": "65225",
"postal_state": "WA",
"postal_country_id": "AU"
},
"contact_first_name": "Joe",
"contact_last_name": "Bloggs"
}

我运行这个小脚本只是为了看看我是否可以正确获取数据。

function messingWithJson()
{
var html='';
var br='<br />';
var text=myUtilities.loadFile('jsonquestion.json');//This just loads into text from a file
var data=JSON.parse(text);
html+=br + Utilities.formatString('%s<br/>%s %s<br />%s', data.created_at,data.contact.first_name,data.contact.last_name,data.contact.company_name)
html+='<br /><input type="button" value="Close" onClick="google.script.host.close();" />';
var userInterface=HtmlService.createHtmlOutput(html).setWidth(800);
SpreadsheetApp.getUi().showModelessDialog(userInterface, 'JSON Question');
}

这是我的输出。

2012-11-13 12:35:57
Joe Bloggs
Acme Limited

我能够恢复我想要的东西。如果您需要有关 JSON 的帮助，不妨查看这些视频。 查看播放列表中间的 JSON 查找。