Add to Spreadsheet

[vickir...@gmail.com]

I am unsure what is going wrong. The error is

"Expected hash. status_O evaluated instead to freemarker.template.SimpleSequence on line 1, column 3 in Save reconciled Transactions."

It makes sense to me that I get this error because the Input Variable "entry" is not an array / JSON array within an array. However, I believe I have done all the coding correctly from everything I read about the looping task for connector "Add a row [V3]". I have attached the "Function", "Connector" "Loop" and "Input Variable" screen images.

I have no problem connecting to my spreadsheet but the "add a row" fails.

I cannot understand why we need to put ${status_O.title} as the name of the array on the multi-instance loop. When I used the loop for sending multiple emails I just had to enter the array name (${status_O}.

In the input variables I also don't understand why we put <${array.variable[P_index]}> in the <gsx> tag where with the email we had ${array[P_index].variable} but I have tried both and I still get the same error.

I hope someone can point out an obvious error I am making.

Also with respect to the Google API Login / Login Spreadsheet connector, is there any way to change the "password" input parameter to a password hidden type? For demonstrations to others, seeing the password in the clear is not great for security discussions. Also, when looking at the process report parameters, the password is very obvious. I cannot use RMP internal credentials as my google apps and docs have different credentials.

[Gareth Thiveux]

Hi Victoria,

First thing I would need to know is, what's your status_O variable. Is it related to a widget array from your Web interface ?

Or is it an array you've built yourself ?

In the first case, you have to know that widget arrays return a JSON of arrays structure, and not an array (so it contains columns in fact, not rows), like this : {"column1":["row1.1","row2.1","row3.1"],"column2":["row1.2","row2.2","row3.2"],"column3":["row1.3","row2.3","row3.3"]}.

But if you have created an array structure yourself, it should then probably be like this : ["value1","value2","value3"]

So based on the situation you are in, you'll have to use different variables for the array you want to use to loop on.

First case : ${my_array.column1} (so seems to be ${status_O.title} in your case, or any other column in fact, it doesn't matter)

Second case : ${my_array} (${status_O} in your example)

And based on this situation, it then also changes the way you call the value you want :

${my_array.column1[P_index]} in the first situation

${my_array[P_index]} in the second situation

Just to give you a tip, if you are in the first situation, you could use the transpose function that converts a JSON of arrays structure into an array of JSONs structure.

So you array that was :

{"column1":["row1.1","row2.1","row3.1"],"column2":["row1.2","row2.2","row3.2"],"column3":["row1.3","row2.3","row3.3"]}

${transpose(my_array)}

[{"column1":"row1.1","column2":"row1.2","column3":"row1.3"},{"column2":"row2.1","column2":"row2.2","column3":"row2.3"},{"column3":"row3.1","column2":"row3.2","column3":"row3.3"}]

So in this third situation, you would use ${my_array} as the array to loop on and ${my_array.column1[P_index]} to get the value you want.

Regarding your password issue, you could think of storing it into your metadata or preferences (cf. Account configuration tab).

You then have specific Freemarker functions you can use in order to retrieve this data (get_preferences, get_user_metadata) : http://docs.runmyprocess.com/API_Reference/FM/User.html

Hope I was clear enough,

Regards,

Gareth THIVEUX

Customer Solutions Engineer

 Fujitsu RunMyProcess

3 rue de Gramont, 75002 Paris - France

website : Fujitsu RunMyProcess

     

Fujitsu RunMyProcess user? Please add a review on GoogleApps Marketplace

Afin de contribuer au respect de l'environnement, merci de n'imprimer ce message qu'en cas de nécessité.
Be environmentally friendly: do not print this email unless it is entirely necessary.

--
Fujitsu - RunMyProcess
---
You received this message because you are subscribed to the Google Groups "RunMyProcess Support Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to supportforum...@runmyprocess.com.
To post to this group, send email to suppor...@runmyprocess.com.
Visit this group at http://groups.google.com/a/runmyprocess.com/group/supportforum/.
To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/e118e851-976f-454b-80c4-f31e9c1b58d1%40runmyprocess.com.
For more options, visit https://groups.google.com/a/runmyprocess.com/d/optout.

[vickir...@gmail.com]

On Friday, September 5, 2014 7:01:37 PM UTC+10, Gareth Thiveux wrote:
> Hi Victoria,

>
>
> First thing I would need to know is, what's your status_O variable. Is it related to a widget array from your Web interface ?
> Or is it an array you've built yourself ?
> In the first case, you have to know that widget arrays return a JSON of arrays structure, and not an array (so it contains columns in fact, not rows), like this : {"column1":["row1.1","row2.1","row3.1"],"column2":["row1.2","row2.2","row3.2"],"column3":["row1.3","row2.3","row3.3"]}.
> But if you have created an array structure yourself, it should then probably be like this : ["value1","value2","value3"]
>
>
> So based on the situation you are in, you'll have to use different variables for the array you want to use to loop on.
> First case : ${my_array.column1} (so seems to be ${status_O.title} in your case, or any other column in fact, it doesn't matter)
> Second case : ${my_array} (${status_O} in your example)
> And based on this situation, it then also changes the way you call the value you want :
> ${my_array.column1[P_index]} in the first situation
> ${my_array[P_index]} in the second situation
>
>
> Just to give you a tip, if you are in the first situation, you could use the transpose function that converts a JSON of arrays structure into an array of JSONs structure.
> So you array that was :
> {"column1":["row1.1","row2.1","row3.1"],"column2":["row1.2","row2.2","row3.2"],"column3":["row1.3","row2.3","row3.3"]}
> ${transpose(my_array)}
> [{"column1":"row1.1","column2":"row1.2","column3":"row1.3"},{"column2":"row2.1","column2":"row2.2","column3":"row2.3"},{"column3":"row3.1","column2":"row3.2","column3":"row3.3"}]
>
>
> So in this third situation, you would use ${my_array} as the array to loop on and ${my_array.column1[P_index]} to get the value you want.
>
>
>
>
> Regarding your password issue, you could think of storing it into your metadata or preferences (cf. Account configuration tab).
> You then have specific Freemarker functions you can use in order to retrieve this data (get_preferences, get_user_metadata) : http://docs.runmyprocess.com/API_Reference/FM/User.html
>
>
> Hope I was clear enough,
>
>
>
>
> Regards,
>
>
>
>
> Gareth THIVEUX
>
> Customer Solutions Engineer

>  Fujitsu RunMyProcess
> 3 rue de Gramont, 75002 Paris - France
> website : Fujitsu RunMyProcess
>
>
>      
>
>
>
>
>
>
>

> Fujitsu RunMyProcess user? Please add a review on GoogleApps Marketplace
>
>
>
>
>
> Afin de contribuer au respect de l'environnement, merci de n'imprimer ce message qu'en cas de nécessité.
> Be environmentally friendly: do not print this email unless it is entirely necessary.
>
>
>
> 2014-09-05 9:51 GMT+02:00 <vickir...@gmail.com>:
> I am unsure what is going wrong.  The error is
>
>
>
> "Expected hash. status_O evaluated instead to freemarker.template.SimpleSequence on line 1, column 3 in Save reconciled Transactions."
>
>
>
> It makes sense to me that I get this error because the Input Variable "entry" is not an array / JSON array within an array.  However, I believe I have done all the coding correctly from everything I read about the looping task for connector "Add a row [V3]".  I have attached the "Function", "Connector" "Loop" and "Input Variable" screen images.
>
>
>
> I have no problem connecting to my spreadsheet but the "add a row" fails.
>
>
>
> I cannot understand why we need to put ${status_O.title} as the name of the array on the multi-instance loop.  When I used the loop for sending multiple emails I just had to enter the array name (${status_O}.
>
>
>
> In the input variables I also don't understand why we put <${array.variable[P_index]}> in the <gsx> tag where with the email we had ${array[P_index].variable} but I have tried both and I still get the same error.
>
>
>
> I hope someone can point out an obvious error I am making.
>
>
>
> Also with respect to the Google API Login / Login Spreadsheet connector, is there any way to change the "password" input parameter to a password hidden type?  For demonstrations to others, seeing the password in the clear is not great for security discussions.  Also, when looking at the process report parameters, the password is very obvious.  I cannot use RMP internal credentials as my google apps and docs have different credentials.
>
>
>
> --
>
> Fujitsu - RunMyProcess
>
> ---
>
> You received this message because you are subscribed to the Google Groups "RunMyProcess Support Forum" group.
>
> To unsubscribe from this group and stop receiving emails from it, send an email to supportforum...@runmyprocess.com.
>
> To post to this group, send email to suppor...@runmyprocess.com.
>
> Visit this group at http://groups.google.com/a/runmyprocess.com/group/supportforum/.
>
> To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/e118e851-976f-454b-80c4-f31e9c1b58d1%40runmyprocess.com.
>
> For more options, visit https://groups.google.com/a/runmyprocess.com/d/optout.

Gareth,

Again thankyou for the thorough and prompt response. I did build my array myself from a mongodb collection maintained in other processes.

I am still getting an error with the "add a row [v3]" connector. Code 400, Bad Request. I have attached the results of the test of the connector. I have copied the authority code, spreadsheet and od6 into the variables along with the entry array values from process report just run. In the attached the P_header is shown and the connector details are:

https://spreadsheets.google.com/feeds/list/${spreadsheet_id}/${worksheet_id}/private/full

<entry xmlns='http://www.w3.org/2005/Atom'
xmlns:gsx='http://schemas.google.com/spreadsheets/2006/extended'>
${entry}
</entry>

[Bidisha Das]

Hi Victoria,

Please check the format of "entry" variable in your "Add a row" connector.The error which you are getting i.e "Bad Request" means that you are inserting wrong input parameters,So kindly follow this format as written in the connector description: <gsx:companyname>"My company"</gsx:companyname>   where companyname is the column name of your spreadsheet and "My Company" is the value you want to insert in this particular column.

You just need to replace both with your own column name and value and for all entries,you need to follow this format.Please check the connector description for "entry" in "Get a row [v3]" connector.You will be more clear about it.

Thanks & Regards

Bidisha

                                                                                                       

                                                                                                     

[vickir...@gmail.com]

Bidisha,

My “entry” variable is NOT an array widget (json array within an array). It is a standard array created from collection (mongodb) records. I refer to Gareth’s response to my previous problems at the start of this forum thread when I used the <gsx> tag (Expected hash. status_O evaluated instead to freemarker.template.SimpleSequence on line 1, column 3 in Save reconciled Transactions) which is repeated below.

That said, I still might have the “entry” incorrectly formed. I just have a freemarker list of variables ${status_O[P-index].applicant},${status_O[P-index].family_name},${status_O[P-index].given_name} which translates into a “entry= xxxxxx,yyyyyy,…….”. I also tried to assign them to a column and tried something similar to when I assign and insert the array to the database (“applicant”:”${status_O[P_index}.applicant}”,”family_name”:”${status_O[P_index].family_name}”,…………) but that got the same error - Bad Request. So I am a little lost for an answer.




FROM GARETH ...........

[Shobhit Tripathi]

Hi,

Have you checked some of the below points:

1. When you are converting your Array of JSONs/JSON of arrays into free-marker list, will it be converted successfully? Because might be you are getting this error while converting array into list.

2. Is your Array created properly. (You can check this on your executed process report inside the computed parameters).

3. While using Add a Row connector you have to use only those column (in the entry field) which are present in that Spreadsheet.

Note: In the above screen shot (Add a Row connector Launch test) you have to use proper structure in the entry field (like <gsx:spreadsheet_colunm_name>spreadsheet,testing</gsx:spreadsheet_colunm_name>) for testing.

As you mentioned above that while you are using ${my_array.column1[P_index]} that is ${status_O.title[P_index]} (in your case) is working fine for me.

Could you please send the screenshot of your Process report error message and the computed parameter (array structure, list..). So that we can give you the exact solution.

Thanks & Regards,

Shobhit 

--
Fujitsu - RunMyProcess
---
You received this message because you are subscribed to the Google Groups "RunMyProcess Support Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to supportforum...@runmyprocess.com.
To post to this group, send email to suppor...@runmyprocess.com.
Visit this group at http://groups.google.com/a/runmyprocess.com/group/supportforum/.

To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/193268af-33e6-4b90-a5df-383c509c601f%40runmyprocess.com.

[vickir...@gmail.com]

Shobhit,

Thanks so much for your assistance but, as I replied to Bidisha, I am not using a JSON array. I did not get my entry to be added to the spreadsheet from an array widget (which is a JSON array of arrays) so I am not using the converter to create a free marker list. I already have a free marker list as I created it from selecting data I have stored in a collection (mongodb). This was explained in Gareth Thiveux's response to my original post to this thread.

Maybe my spreadsheet is not properly set up! I would have thought the connector API from the RMP server would be allowed to access my spreadsheet provided the auth code retrieved is correct and access token is properly received.

I have attached the process reports as requested.

[Shobhit Tripathi]

Hi Victoria,

Thanks for the Screenshot.

Scenario 1:

AS in the screenshot I found that, the array has been created properly, but you are using the different syntax to pass in the spreadsheet.
You have to use this syntax to pass in the entry field ${status_O[P_index].title}. And also you have to use your array name (${status_O}) in the Loop tab for iterating the loop as per the index(s).

I am attaching the screenshot of my process input variable for your better understanding.

Scenario 2:

As in screenshot, if the entry variable consists of all the records which you wanted to push in the spreadsheet then you have to use entry variable directly like this: <gsx:colunmname>${entry}</gsx:columnname>

Note: Make sure that your column name (both in spreadsheet and process entry field) should not contain any special character, number as well underscore also.(As you are having a column nae given_name, please use some another name or just remove the underscore)

Hope this will help you.
If you still facing the same issue, please let me know.

Thanks & Regards,
Shobhit

To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/f3eeccdf-508e-4e2a-8c66-477429a70f41%40runmyprocess.com.

[Victoria Redwood]

Shobhit,

I cannot find a solution.  I have done exactly as you suggeste but still the "Bad Request".  I have attached:

• Input Variables
• Process Report with the error and the computed variables
• The loop
• The actual spreadsheet

I am so sorry to keep bothering you all but I seem to be retrying things I have done before to no avail.  It is obvious I am doing something wrong because so many others have no issues.

The reason we are doing this is to set up a number of use cases for a large customer for them to better manage their own customers.  One use case involves using a cloud spreadsheet as a information "hub" for customers with simple processes.

Regards,

Vicki

You received this message because you are subscribed to a topic in the Google Groups "RunMyProcess Support Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/a/runmyprocess.com/d/topic/supportforum/3lB2c6vTmaI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to supportforum...@runmyprocess.com.

To post to this group, send email to suppor...@runmyprocess.com.
Visit this group at http://groups.google.com/a/runmyprocess.com/group/supportforum/.

To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/CAPdfL%3DdQCyOaL5ztzVi9kurBDdZtmr2-tNV0_3OSK7o7m-21og%40mail.gmail.com.

For more options, visit https://groups.google.com/a/runmyprocess.com/d/optout.

--

Regards,

 

Vicki

0417 399 205

[Shobhit Tripathi]

Hi Vicki,

As I found in the screenshot you are using angular bracket after the <gsx> tag, which creates problem. Your entry field should be like this:

<gsx:first_name>${arr_test[P_index].firstname}</gsx:first_name>. Do not use this syntax <gsx:first_name><${arr_test[P_index].firstname}></gsx:first_name> as this will behave like an another tag.

Please find the attachment for more clarification.

Feel free to ask for further assistance.

Thanks & Regards,

Shobhit

To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/CAEmPGH2rO7pBeav_hc5K5OV8KG7eO3Ro5RjhsmZDMELCYUXRCg%40mail.gmail.com.

[Victoria Redwood]

Shobhit,

Your latest observations have corrected my process and it runs really well.  I feel such an idiot making such a stupid mistake.  Thank you so very much for taking so much time working through my spreadsheet issue.  I have changed it so many times but just was not careful enough.

Regards,

Vicki

To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/CAPdfL%3DfTtncAhtg5TARLwDc%3DSUmvqTgtqCRJ7XbJtpNmRswUmQ%40mail.gmail.com.

For more options, visit https://groups.google.com/a/runmyprocess.com/d/optout.

[Shobhit Tripathi]

Hi Vicki,

Great to know that it works.

Feel free to ask any time.

Thanks & Regards,

Shobhit

To view this discussion on the web visit https://groups.google.com/a/runmyprocess.com/d/msgid/supportforum/CAEmPGH1k%2BJFYp2WMdkUZM80VsJDX8kmBiY5AmN64F6BHwFk6kg%40mail.gmail.com.