How to use JSONP / JQuery in Scriptlab to call API and write data to excel

67 Views Asked by At

I am trying to use Scriptlab to call the public Census API and write the resulting json response to excel.

Specifically, it takes the [0][0] in the range, creates an API url from that, and it is supposed to write the results into cell [0][1] of the range.

I am able to successfully make the API call, but I cannot understand how to actually write the data to excel. I am trying to execute a context.sync() inside the jsonp callback function, but that is not working. I am not quite sure why--clearly there is something I'm not understanding about callback functions. Here is the complete code.

Note that you can try this with any address in [0][0], for example '1600 Pennsylvania Ave, Washington DC'

    $("#run").click(() => run());

      async function run() {
        await Excel.run(async (context) => {
        const range = context.workbook.getSelectedRange();
        range.load("values");
        await context.sync();


        let url = 'https://geocoding.geo.census.gov/geocoder/geographies/onelineaddress?address=' + encodeURIComponent(range.values[0][0]) + '&benchmark=2020&vintage=2010&format=json';

        $.ajax({
          url: url,
          dataType: "jsonp",
          jsonpCallback: "handleJSONP" + 0,
          success: async function (json) {
            range.values[0][1] = JSON.stringify(json);
            console.log(range.values[0][1]);
            range.values = range.values;
            await context.sync();
          }
        });
      });
    }
0

There are 0 best solutions below