Write back from spreadsheet

[Dat]

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 

[AfeefaTk]

Hi @Dat 

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

Thanks

[Dat]

Hi @AfeefaTk ,

here are the automation and app.

Thanks in advance for your help!

[AfeefaTk]

 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]

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"

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. 

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 · Accepted solution]

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]

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.

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

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.

Any idea ?

Thanks again !

[AfeefaTk · Accepted solution]

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]

Works like a charm!!!

Thanks a lot @AfeefaTk !!!

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

[qingkong]

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

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