1

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();
          }
        });
      });
    }
Ethan
  • 11
  • 2
  • You’re not using office scripts in script lab, you’re using the Office JS framework. You’re confused, so which framework do you actually want to use? – Skin Feb 13 '23 at 20:02
  • Thank you for clarifying. I have edited the title and description to explain that I am using Scriptlab. Candidly, I am just trying to figure out how to get the json response persisted to excel. I am agnostic on frameworks, but it happens that I started in Scriptab, assuming it would be straightforward. – Ethan Feb 13 '23 at 21:44
  • Scriptlab is nothing more than a test tool if we're to be honest. I think Office Scripts is what you'd want. Start here, it will help you ... https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel ... also, re: "calling an API", if you're using Office Scripts, make the call in PowerAutomate/LogicApps and pass the result as a parameter to your script, it tends to be less flakey. – Skin Feb 13 '23 at 21:53
  • 2
    Thank you very much, that's very helpful. I was able to get it to work in Office Scripts using a CORS proxy. – Ethan Feb 13 '23 at 22:28
  • @Ethan feel free to answer your own question to and accept to mark completed! – FreeSoftwareServers Feb 14 '23 at 19:08
  • 1
    @FreeSoftwareServers I’m talking about Office Scripts with the fetch call, not Office-JS. You’re mixing the two technologies. – Skin Feb 14 '23 at 20:27

0 Answers0