Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Dat
Partner - Contributor III
Partner - Contributor III

Write back from spreadsheet

Hi all,

I've seen Mike T's video about creating write back from SQL database : https://youtu.be/hYER2nGKctM , and I was wondering if it is possible to do the same with data from spreadsheet.

I've tried with the "update range/row in sheet" blocks with no success.

Any idea?

 

Qlik Application Automation 

 

 

 

Labels (1)
2 Solutions

Accepted Solutions
AfeefaTk
Support
Support

Hi @Dat 

Yes, I suspect the issue is with the input you have provided in the Values field. It doesn't look like a list.

I have created demo automation for your reference to show how list can be created. In this example, I have used explode formula to convert comma-separated values into a list.

Another method to create a list is to use a variable of the type list and add the Get Expression Value block response as an item to the list.

Please take a look at the example automation, test it out from your end, and let us know your feedback.

Thanks!

View solution in original post

AfeefaTk
Support
Support

Hi @Dat 

The only solution that comes to my mind is having a column in Google sheet that counts the row number for an eg column with name id which has values 1,2,3,4,..etc.

You can fetch this id column in the app and map it to variable in UI and access it from the automation.

Once you have it in the automation , if you already know the alphabetic part of the start cell, you can make the numeric part dynamic by getting the id from the Get Expression Value block and appending it along the alphabetic part of the start cell.

Thanks

View solution in original post

10 Replies
AfeefaTk
Support
Support

Hi @Dat 

Could you please share the app and automation that you've created? 

Thanks

Dat
Partner - Contributor III
Partner - Contributor III
Author

Hi @AfeefaTk ,

here are the automation and app.

Thanks in advance for your help!

AfeefaTk
Support
Support

 Hi @Dat 

Sorry for the delay in response.

Are you getting errors in the automation while trying to update rows in the Excel sheet?

Or is it only that the row is not getting updated, but the automation doesn't fail?

We have noticed some odd behavior with variable selections which is not getting passed to the automation. We are actively investigating this.

Please cross check the response of the Get Expression Value block and see if it picks the updated value from the app.

Do you mind sharing the automation run log file as well?

Thanks

Dat
Partner - Contributor III
Partner - Contributor III
Author

Hi @AfeefaTk ,

When I change any value in the app, the “get expression blocks” block updates properly, but the “update row in sheet” block doesn't.

I think something is wrong with my inputs in “values”. It says that only json lists or comma separated lists are allowed… so maybe what I put in is not considered as such. I don’t know how to convert my “get expression blocks” result into a JSon list.

For exemple, here I'm trying to change "Consultante Qlik Junior" to "Expert BI"

Dat_2-1685795992434.pngDat_3-1685796035337.png

 

Dat_0-1685795794365.png

The weird thing is, when I change a value in the app with “Blank”, I get no error in my automation. But when I reload my data in the app after that, I get no update in my straight table. 

Dat_4-1685796165498.png

 

Dat_1-1685795794372.png

 

Here are the logs when it runs perfectly and when there is a bug.

Tell me if you need more info.

Thanks again for helping!

 

AfeefaTk
Support
Support

Hi @Dat 

Yes, I suspect the issue is with the input you have provided in the Values field. It doesn't look like a list.

I have created demo automation for your reference to show how list can be created. In this example, I have used explode formula to convert comma-separated values into a list.

Another method to create a list is to use a variable of the type list and add the Get Expression Value block response as an item to the list.

Please take a look at the example automation, test it out from your end, and let us know your feedback.

Thanks!

Dat
Partner - Contributor III
Partner - Contributor III
Author

Hi @AfeefaTk ,

Thank you very much!!!

Almost everything is running perfectly! 🎉

Just one more question :

Do you know how to make the start cell space dynamic ?

In the exemple below, it's only updating Cell C3.

I don't know if it's possible to automatically  update this space according to my selections in the app.

Dat_0-1685962269993.png

 C3 = 3 (Column title = Ancienneté) in my sheet :

Dat_0-1685966630786.png

If in my app I select Kenny and want to update Kenny's Ancienneté (from 6 to 23), it won't update Cell C5 (Kenny), but C3.

Dat_1-1685966742081.png

Dat_2-1685966907331.png

 

Any idea ?

Thanks again !

 

 

 

AfeefaTk
Support
Support

Hi @Dat 

The only solution that comes to my mind is having a column in Google sheet that counts the row number for an eg column with name id which has values 1,2,3,4,..etc.

You can fetch this id column in the app and map it to variable in UI and access it from the automation.

Once you have it in the automation , if you already know the alphabetic part of the start cell, you can make the numeric part dynamic by getting the id from the Get Expression Value block and appending it along the alphabetic part of the start cell.

Thanks

Dat
Partner - Contributor III
Partner - Contributor III
Author

Works like a charm!!!

Thanks a lot @AfeefaTk !!!

I'll try with Excel now to see if it's possible🤞

qingkong
Contributor II
Contributor II

您好,这个json怎么使用的,有示例演示吗。因为我看了介绍还是不理解

Hello, how to use this json, is there an example demonstration? Because I read the introduction and I still don't understand