I'm trying to write a script in Google Sheets that uses an ISBN number to query the Amazon Product Advertising API for product information. I'm totally stuck parsing the XML. I've exhausted my knowledge and could use some help understanding this.
So far this is what I have:
function myFunction(isbn) {
var app = SpreadsheetApp;
var activeSheet = app.getActiveSpreadsheet().getActiveSheet();
var endpoint = 'webservices.amazon.com',
uri = '/onca/xml';
var access_key_id = '**************',
secret_key = '**********************',
associate_tag = '****************';
var params = {
Service: 'AWSECommerceService',
Operation: 'ItemLookup',
AWSAccessKeyId: access_key_id,
AssociateTag: associate_tag,
ItemId: '9780892131341', // Hardcoded an ISBN for testing
SearchIndex: 'Books',
IdType: 'ISBN',
ResponseGroup: 'ItemAttributes, Images, Offers',
Timestamp: new Date().toISOString()
};
//sort params by keys
var canonical_query_string = Object.keys(params).sort();
// urlencode
canonical_query_string = canonical_query_string.map(function(key){
return key + '=' + encodeURIComponent(params[key]);
});
// Generate canonical query
canonical_query_string = canonical_query_string.join('&')
var string_to_sign = 'GET\n' + endpoint + '\n' + uri + '\n' + canonical_query_string;
var signature = Utilities.base64Encode(Utilities.computeHmacSha256Signature(string_to_sign, secret_key));
var request = 'http://' + endpoint + uri + '?' + canonical_query_string + '&Signature=' + encodeURIComponent(signature);
Logger.log(request)
var response = UrlFetchApp.fetch(request, {muteHttpExceptions: true});
var data = XmlService.parse(response);
var elements = data.getRootElement().getChildren();
Logger.log((elements));
elements.forEach(function(e) { Logger.log(e) })
}
Sorry the XML formatting got messed up while copy/pasting, but I think it'll do.
I can't seem to traverse the document the way I would expect to. If someone could help me understand what I'm missing, that would be greatly appreciated, thanks!
EDIT:
I managed to get the ASIN number:
var item1 = items[1].asElement();
var item1_children = item1.getChildren();
var item1_asin = item1_children[0].getAllContent();
Outputs:
[18-02-11 22:07:33:500 PST] [0892131349]
When I try to run getAttribute('ASIN') or getChild('ASIN') I get back null
How can I write this so that I can utilize the getAttribute(String name) or getChild(String name) methods?
I'm trying to write a script in Google Sheets that uses an ISBN number to query the Amazon Product Advertising API for product information. I'm totally stuck parsing the XML. I've exhausted my knowledge and could use some help understanding this.
So far this is what I have:
Logging
elementsgives me:elements.forEach(function(e) { Logger.log(e) })outputs:elements.lengthgives me2.0typeof(elements[0])gives meobjectRunning
elements[0]gives me:[Element: <OperationRequest [Namespace: http://webservices.amazon.com/AWSECommerceService/2011-08-01]/>]Here is the full XML response:
Sorry the XML formatting got messed up while copy/pasting, but I think it'll do.
I can't seem to traverse the document the way I would expect to. If someone could help me understand what I'm missing, that would be greatly appreciated, thanks!
EDIT:
I managed to get the ASIN number:
Outputs:
[18-02-11 22:07:33:500 PST] [0892131349]When I try to run
getAttribute('ASIN')orgetChild('ASIN')I get backnullHow can I write this so that I can utilize the
getAttribute(String name)orgetChild(String name)methods?