Generate keywords with Amazon's Autocomplete API

appscript_amazon_autocomplete_api_minimal.js

// This allows us to add a custom menu to Google Sheets so users can trigger without diving into the code
function onOpen() {
    options = [
      {name:"Amazon Autocomplete Lookup", functionName:"query_amazon_autocomplete"},
    ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("* Tools * ", options);
}

function query_amazon_autocomplete() {
    var sheet = SpreadsheetApp.getActiveSheet()

    for (var i = 1; i < sheet.getLastRow()+1; i++) {
        var keyword = sheet.getRange(i,1).getValue();
        var next_cell_over = sheet.getRange(i,2).getValue();

        // ensure input is not empty and adjacent cell is empty to avoid errors/overwrites
        if (next_cell_over == "" && keyword != "") {
            try {
                var url_compiled = 'http://completion.amazon.com/search/complete?mkt=1&search-alias=aps&x=updateAmazon&q=' + keyword
                var response = UrlFetchApp.fetch(url_compiled);
                SpreadsheetApp.getActiveSpreadsheet().toast(String(response))
                response = String(response).split(" = [")[1];
                response = response.split("],[{")[0];
                response = response.split(",[")[1];
                response = response.split('"').join('');
                var results_list = response.split(',')

                sheet.getRange(i, 2, 1, results_list.length).setValues([results_list]);
            } catch(e) {
            };
        }
    }
}