How to fetch data from google excel sheet into thingworx data tabel?

[pagrawal-2]

I am able to fetch data from csv into thingworx data table.

But I need to fetch data from Google Excel Sheet into TWX.

How can I achieve that?

Please help!!!

[aseaton]

Hi Prashant,

Have a look at the Google API Reference API Reference  |  Drive REST API  |  Google Developers. In ThingWorx > Add a service > Snippets > ContentLoaderFunctions library you should be able to use the service GetXML() to make a HTTP GET request and retrieve your information from your google spreadsheet.

BW,

Andy

[pagrawal-2]

Hi Andy,

Thanks for helping me out.

I am facing a problem which is while using that GetXML() snippet and giving the URL of the Google Sheet in its params,

I am getting this:

Now can u please help me out with this.

I tried to resolve but did not found any solution.

Thanks.

[aseaton]

Yeah, I see the same issue for me. I have tried using the GetJSON() service & it seems a bit happier.

I think what we may need is here: https://developers.google.com/drive/v3/reference/files/export#try-it

We can use the export request to get the content of the file. The fileId and the mimeType are needed as the request parameters: have a look here for the mimeType Supported MIME Types  |  Drive REST API       |  Google Developers

We should be able to get the content this way, into ThingWorx.

This is result so far. I can get the JSON and then i can convert to string:

Andy

[aseaton]

Do you have any advice Polina Osipova​?

[posipova]

I'm currently on PTO, will look into this once I'm back in the office.

[pagrawal-2]

Hi Andy,

Let take this way like I do have a google sheet with name "Records".

This is the URl of that : Records - Google Sheets

I am hitting this URL from TWX service but just getting nothing under Results Column on output.

And I used JSON Service.

As u said if through get xml it is kind a tricky then I just want for time being to have data from google sheet in twx.

I made that sheet public, if u want then u can us ethe url and check if u can fetch the data.

Hope you are getting me.

Please catch me up here.

Thanks.

[pagrawal-2]

Hi Andi,

Got any chance to look into that.

Thanks.