Google Script Mapping

I was trying to use mapping on Google Script to enter a value on the City (id="city") field and show the Price (id="price") with the script below. The code below doesn’t work and didn’t provide any result on the price textbox. Moreover, would it be possible to do a multiple conditions? I mean the price will be displayed base on the City, Products, and Quantity. I have provided the image below. Thanks!

  <script>
 
    document.getElementById("btn").addEventListener("click",getPrice);

    function getPrice(){
      vCity = document.getElementById("city").value;
      google.script.run.withSuccessHander(updatePrice).getAmount(vCity);

    }

    function updatePrice(myprice) {
      document.getElementById("price").value = myprice;
    }

  </script>

Here’s my gs.

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile("page");
}


function getAmount(city) {
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1FhWlaXixXPxq8EnHICfVR207aIkxtkzOgvdvt3hHuuI/edit#gid=0");
  var ws = ss.getSheetByName("Pricing");
  var data = ws.getRange(1, 1, ws.getLastRow(),2).getValues();

  var cityList = data.map(function(r){ return r[0]; });
  var priceList = data.map(function(r){ return r[1]; });
  var position = cityList.indexOf(city);

  if(position > -1){
    return priceList[position];
  } else {
    return "Unavailable";
  }

image

1 thought on “Google Script Mapping”

  1. This works for me. I ran it as a dialog. I changed the sheetname.

    function launchmypricedialog() {
      let html = '<input type="text" id="city" placeholder="City" /><br /><input type="text" id="price" /><br /><input type="button" value="Click Me" id="btn"/><script>document.getElementById("btn").addEventListener("click",getPrice);function getPrice(){let vCity = document.getElementById("city").value;google.script.run.withSuccessHandler(updatePrice).getAmount(vCity);}function updatePrice(myprice) {console.log(myprice);document.getElementById("price").value = myprice;}</script>';
      SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'test');
    }
    
    function getAmount(city) {
      var ss = SpreadsheetApp.getActive();
      var ws = ss.getSheetByName("Sheet1");
      var data = ws.getRange(2, 1, ws.getLastRow()-1, 2).getValues();
      var cityList = data.map(function (r) { return r[0]; });
      var priceList = data.map(function (r) { return r[1]; });
      //console.log(city);
      //console.log(JSON.stringify(cityList));
      //console.log(JSON.stringify(priceList));
      var position = cityList.indexOf(city);
      //console.log(priceList[position]);
      if (position > -1) {
        return priceList[position];
      } else {
        return "Unavailable";
      }
    }
    

    This was my data

    COL1 COL2
    City1 12
    City2 2
    City3 24
    City4 18
    City5 14
    City6 3
    City7 17
    City8 5
    City9 29
    City10 24
    City11 8
    City12 26
    City13 15
    City14 25
    City15 0
    City16 25
    City17 17
    City18 11
    City19 22
    City20 10
    City21 16
    City22 0
    City23 6
    City24 22
    City25 15
    City26 11
    City27 13
    City28 14
    City29 22
    City30 24

    My Dialog:

    enter image description here

    Reply

Leave a Comment