Qlik Automation - Export data to Excel Site on Sharepoint

[AshB]

Hi, I am trying to use the Automation tool to get some data into excel.  The excel file is on a sharepoint that I have valid connections to.  However when I try to get the "Add table to Worksheet" block to work, I am having trouble with the Drive ID.  I have tried:

Getting the "ID" from the "List Drives from Site Block" via lookup

Getting the "ID" from the "List Drives from Site Block" into a Variable and then using that

Getting the "Web URL" from the "List Drives from Site Block" via lookup

Getting the "Web URL" from the "List Drives from Site Block" into a Variable and then using that

Trying to type the address in directly into the Drive ID field

None of these work, have you got any explicit examples please?

[Emile_Koslowski]

Hi @AshB ,

Here's a getting started article on getting data into Excel:  https://community.qlik.com/t5/Knowledge/Sync-data-to-Microsoft-Excel-sheet-using-automations-small/t...

But it's strange that the lookup doesn't contain the drive id, are you sure you're using the right account? For example, if you've connected multiple accounts for the same connector, you need to highlight the account you want to use for every block. Else the first account is used as default.

Kind regards,
Emile

[Emile_Koslowski]

And here's an article on using SharePoint drives in combination with the Microsoft Excel connector: https://community.qlik.com/t5/Knowledge/How-to-Work-with-Excel-files-stored-on-Sharepoint-using-Qlik...

[AshB]

Hi, Thanks for the reply, I suspect it may be multiple Drive Ids causing the problem, so deliving into that.

Quick question though, should the output be like : 

With regards to the "Graph.microsoft.com" part of it?

[Emile_Koslowski]

Yes, that looks good to me and yes graph.microsoft.com is the base URL for that API request. Could you show the full error message?

[AshB]

Hi, I managed to get a drive ID via the lookup that worked, Im just trying to get a Drive ID from https://community.qlik.com/t5/Knowledge/How-to-Work-with-Excel-files-stored-on-Sharepoint-using-Qlik... to work.  Using the Get ID and placing that in, doesnt seem to work

[Emile_Koslowski]

Hi,

Is this a personal drive in SharePoint or is it linked to a group or site?

Emile

[AshB]

Its a work site. So briefly it looks like this:

So the conditions are selecting the correct site, and then writing that into the variable DriveID:

And then trying to use that ID as the excel is where it is not working.

[Emile_Koslowski · Accepted solution]

It's hard to see what could be going wrong here.

I take it that you have sufficient permissions in Sharepoint/Excel to do all these steps and the file already exists in the drive?

1. List sites
2. List drives from site
3. Write to drive

If the above checks out, could you maybe share an export of your automation with me by sending it to emile.koslowski@qlik.com? (right-click the canvas in the automation editor and select "Download workspace")

Emile

[paulcalvet]

Hello @Emile_Koslowski ,

Use SharePoint with automation is really difficult compared to others connectors.

Standard users doesn't understand what they have to do (the others connectors are very easy to use and ther is no problem with them)

Can we expect new process (like when you add a data connection for example 😀).

Thanks !