Has anyone set up an API to have Google Sheets GET information from a table. For example, how hard is it to run a job that will update a Google Sheets file with the current data in the incident table?

cjdiaz · ‎11-30-2015

dmfranko · ‎12-01-2015

It would be a GET. You'll want to go to script.google.com and create a script that looks something like the below to fetch the data. This example is pulling everything in the incident table. Parsing the json and loading it into a sheet I think is beyond the scope of this forum. There's a lot of info out there about how to do this once you've got the json.

function myFunction() {

   url = "https://dev15112.service-now.com/api/now/table/incident"

   

   var options = {

       "headers":

       { 

           "Authorization": "Basic " + Utilities.base64Encode("user:password"),

           "contentType": "application/json"

       }

   };

   

   var response = UrlFetchApp.fetch(url,options);

   // Here you can see what the response was

   Logger.log(response);

}

View solution in original post

dmfranko · ‎11-30-2015

Haven't done that specific thing with Google Sheets, but it shouldn't be too hard to script with something like Google Apps Script. I don't think you'll run into cross domain issues, but that'll be something to keep an eye on. Depending on your need it might be easier to just write a UI page with a table instead?

cjdiaz · ‎12-01-2015

To go further in detail here is what I am trying to do. I am working on a project to report on stories within our SDLC module, specifically the story table. Right now, we manually update a Google Sheets file with the story table daily. This then runs plenty of reports based on the raw data. These reports and graphs (Google Charts) are then displayed as iFrames in various dashboards and places to show metrics. We needed something more than the Reporting module in SNOW. This is why we export and handle the data in Google Sheets.

We just need to somehow update a sheet in our document with the most current version of the story table about once to twice daily. (4 am)

I am an intern and I am new to API's but have worked with SNOW for several months now. Would I write a Google script to GET from the URL of the SNOW instance? How would it authenticate the request?

Any advice is highly appreciated!

dmfranko · ‎12-01-2015

You'll want to use ServiceNow's table api. It's pretty easy to use and at the same time pretty powerful. What I would suggest is creating a new user just for this purpose, which you'll need to give the rest_service role and probably itil as well. You authenticate via basic auth, although you have some other options in addition depending what version you're on.

cjdiaz · ‎12-01-2015

Okay, would I use a GET or POST request? I know how to configure them in SNOW, but where do I put the code for the two applications to communicate?