topic Data formatting issue while extracting data from Google Spreadsheets by using Rest Connection in Connectivity & Data Prep

Data formatting issue while extracting data from Google Spreadsheets by using Rest Connection

Fri, 02 Apr 2021 07:51:39 GMT

I have data in google spreadsheet which is protected and able to access the data through rest connector. Now, the issue is with the formatting i.e. In my google sheet, having two columns Name and Status with respective values. After successful connection in Qlik Sense, able to see all those values in a single column including the headers.

My Google sheet structure is

And the retrieved data is

The Auto generated script is

LIB CONNECT TO [GS];

[values]:

LOAD

[@Value],

[__FK_values_u0] AS [__KEY_values];

SQL SELECT

(SELECT

"@Value",

"__FK_values_u0"

FROM "values" FK "__FK_values_u0" ArrayValueAlias "@Value")

FROM "values" PK "__KEY_values" FK "__FK_values")

FROM "valueRanges" PK "__KEY_valueRanges" FK "__FK_valueRanges")

FROM JSON (wrap on) "root" PK "__KEY_root";

Please help me in getting the data column wise.

Re: Data formatting issue while extracting data from Google Spreadsheets by using Rest Connection

Darren_Ball — Fri, 24 Feb 2017 08:13:15 GMT

Hi,

Why don't you try the Qlik Google Drive and Spreadsheets Connectors, which is a free to use, Standard Connector as part of the Qlik Web Connectors Suite?

You can get it from here: Qlik Market

This is a dedicated connector to get your Google Sheets and may solve your issue.

Thanks,

Darren

Re: Data formatting issue while extracting data from Google Spreadsheets by using Rest Connection

Mon, 27 Feb 2017 06:28:48 GMT

Hi Darren,

Thanks for your quick response..

Yes, I knew about it but I didn't get any source to download Qlik Google Drive and Spreadsheets Connectors.

I tried to get the connector from Analyze Data from Databases, Spreadsheets, Websites, and Custom Sources . There is two references - Qlik Customer Download Site is redirecting to QV download page and Qlik Market is not loading at all.

I read somewhere that all these connectors are part of REST and implemented in Qlik Sense but I'm not sure about it.

Thanks,

Prashanth Reddy

Re: Data formatting issue while extracting data from Google Spreadsheets by using Rest Connection

Darren_Ball — Mon, 27 Feb 2017 08:45:40 GMT

Hi,

Looking at your Qlik Market link, the url is missing '/solutions/' as part of the URL and should be

http://market.qlik.com/solutions/Qlik_Google_Drive_&_Spreadsheets_Connector

On the download site, you need to go to the Qlik Connectors area/tab and pick Qlik Web Connectors from the list:

To confirm the Qlik Web Connectors are currently are not integrated into Qlik Sense or QlikView, but is a separate product that works along side them.

I would be interested to see where you read about them being part of REST and integrated into Qlik Sense, so if you have the link, please send it to me so that I can fact-check it.

I hope this clarifies any confusion.

Darren

Re: Data formatting issue while extracting data from Google Spreadsheets by using Rest Connection

Mon, 27 Feb 2017 11:00:11 GMT

Hi Darren,

I'm unable to access the url http://market.qlik.com/solutions/Qlik_Google_Drive_&_Spreadsheets_Connector

Its just loading for ever.

Is that the image attached by you is captured recently or you referring to the old one. Why I'm asking means, the url is not working for me. I tried 100s of times in different browsers by enabling Pop-Up blockers too and also in three systems.

Regarding the missing word '/solutions/' in my earlier post, I have accessed that url from Analyze Data from Databases, Spreadsheets, Websites, and Custom Sources against Google Drive & Spreadsheets. (Qlik Market)

If I'm able to access that url, my problem will be solved.

Thanks,

Prashanth Reddy

Re: Data formatting issue while extracting data from Google Spreadsheets by using Rest Connection

Darren_Ball — Wed, 01 Mar 2017 10:21:46 GMT

Hi,

That screenshot was taken a few days ago from the qlik demo site http://demo.qlik.com/download‌ where you can follow that screenshot to find it.

Re: Data formatting issue while extracting data from Google Spreadsheets by using Rest Connection

Wed, 01 Mar 2017 10:39:24 GMT

Hi Darren,

But I'm unable to see Qlik Connectors tab or Web Connectors. I can see only below page when I open http://demo.qlik.com/download

As you mentioned in your previous replies, its free to use. So, is it any login specific to view or download the mentioned connectors.

Thanks.

Re: Data formatting issue while extracting data from Google Spreadsheets by using Rest Connection

juraj_misina — Thu, 01 Feb 2018 23:32:51 GMT

Hello Prashanth,

were you able to resolve this issue? I'm trying to load data from a Google Sheet via REST connector and am facing the exact same challenge. I'd like to avoid using Qlik Web Connectors if possible.

Thanks.

Juraj