Bridge: Google Translate integration

akhalid

Hi,

I am using Smartsheet Bridge to translate ONE column of a sheet that has a couple of 100 rows. I've tested Google Translate integration on a small data set and it works.

I have the following questions:

a) I provide Google Translate, reference to an array of 100 strings and configure it to translate. I expect it to respond with an array of 100 strings in the specified language. Instead I get an array of 1 string that is very very long and has some of the elements of the input array. E.g. for an array of 100, it was returning roughly 50 translated rows (concatenated together in a string).

How can I get Google Translate to respond with an array of equal elements IN the translated language?

b) What is the maximum string length that the Google Integration can handle?

Regards,

AK

Kiki_7308

I too am looking for more info and feel if Smartsheet has a google translate option there should be a free class on using it. Anyone?

akhalid

Would very much appreciate if the likes of @Genevieve P. , @Paul Newcome , @Andrée Starå can provide some insight.

Regards,

AK

Paul Newcome

@akhalid I am not familiar with Bridge. I only have very basic knowledge of it, so please forgive me if I am asking a silly question, but... What if you ran it on a row by row basis instead of on the entire sheet? It looks to me like you are concatenating all 100+ rows into a single string prior to translating which is why the translation is coming out as a single string. The reason it isn't outputting the entire thing translated is possibly because there is a 4,000 character limit in a cell and that's where your long translation hits the 4,000 characters.

Again... Just a guess coming from very little experience with Bridge.

akhalid

@Paul Newcome, thank you for your response.

I fell back to running row-by-row, but then the problem comes, who do I write it one-shot as a bulk operation back into Smartsheet? The translation is happening in a Child workflow and runs for the number of rows. I am currently updating one row at a time in Smartsheet from within the Child workflow, which you can imagine would take a lot of time for a 700 row sheet.

Collecting the result from each child workflow, forming an array of it is not "natively" supported. I have some ideas, but they are not without jumping through some hoops.

Regards,

AK

jmyzk_cloudsmart_jp

Hi @akhalid

I would use a child workflow to translate and update each row.

When using the Smartsheet API, we can use the Update Rows method. So, we can create an array of rows and then update rows with a single request. However, the Bridge's Smartsheet Integration does not seem to have "Update Rows"; It has "Update Row". That's why I used the Child workflow.

Sample Sheet to translate English column values to other languages

Parent Workflow

Get a sheet and pass the rows to the child workflow.

(I did not know how to pass the sheet ID, so I used the "Child entry values".)

Child Workflow

First, get a row to get the English column cell value and the text to translate.

Then, Translate using the value. (One state for each language.)

Text To Translate: {{states.startstate.smartsheet.get_row.row.cells.English.displayValue}}

Lastly, update the row with the translation results.

akhalid

Hi @jmyzk_cloudsmart_jp ,

Thank you for taking the time out to give such a detailed response with screenshots! 😀

I am doing exactly what you are doing, however, I want to speed things up. You mentioned, "So, we can create an array of rows and then update rows with a single request."

Updating with a single request is not a problem for me because I can construct the packet in JavaScript module and then update with a single request using the Call API module.

Can you please share some ideas on how to get the result of all the translations into an array of rows? Just like you have your parent workflow that passes the array of English rows to the Child workflow, how do you receive an array of translated rows back into your parent workflow so that it can be updated one-shot ?

Regards,

AK

Paul Newcome

Is it possible to add the same character to the end of each row's data as a delimiter of sorts and then leverage that when outputting into the column?

Genevieve P.

Hey @akhalid

Are you adding the entire {array} into the "Text to Translate" section of the Google Translate module? If so, I believe it's reading the array as if it's the CSV output (with commas between array values) instead of as separate "rows".

You would need to loop through a Child Workflow if you want each individual row to be translated as a separate line item, versus all together as one text.

Or, as Paul noted, if you have an easy way to identify the "end" of each string after translation (such as a unique character or specific word) I believe you could use JS to create an array from the combined Google Translated text. Even if you did that though, I expect you would need a Child Workflow to loop through that new array to update the correct row with the specific array output.

Since you're working with Child Workflows anyway, I'd personally start the child run earlier and skip the extra JS step - just like @jmyzk_cloudsmart_jp suggested!

akhalid

@Paul Newcome , @Genevieve P. ,

Thank you for showing interest in the solution.

Yes, I have tried two approaches:

• Put a magic string at the end of the English string that will not get translated, e.g. <<A5A5>>
• Put the magic string at the end of the translated string instead

Then extract the result into separate elements of an array.

For me the only challenge is to build up the string of response(s) delimited by a magic character. I had some success at it, but stopped short because I was unsure how many characters the String: Compose Text module can hold. Any ideas @Genevieve P. ?

Is it more efficient (and best practice) to hold the concatenated response in a String: Compose Text module, or using Array Management: Add Object To Array ?

Regards,

AK

akhalid

BTW, I reached out to Smartsheet support. I'll share the response for the benefit of the wider community.

They have suggested that assembling the result in an array, then transforming it to a string, calling Google Translate and then splitting the result will be a bit more efficient than accumulating the input in a string. 

Its good to know that String: Compose Text should be working fine with millions of characters. 😀

The sad news is that Google Translate API has some limitations for input strings being no more than 128 in a single call. 😟

Since it is not possible to expand the array, please submit your feature request to the Product team.

Regards,

AK

Genevieve P.

Hey @akhalid

Thank you for sharing the response and follow-up!