Skip to content

Instantly share code, notes, and snippets.

@alecbw

alecbw/appscript_amazon_autocomplete_api_minimal.js

Created October 21, 2022 05:46
Show Gist options
  • Save alecbw/b96076858f829a2e39b165eee99c358d to your computer and use it in GitHub Desktop.
Save alecbw/b96076858f829a2e39b165eee99c358d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. alecbw created this gist Oct 21, 2022.
    33 changes: 33 additions & 0 deletions appscript_amazon_autocomplete_api_minimal.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,33 @@
    // 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) {
    };
    }
    }
    }