GOOGLETRANSLATE with Arrayformula?

Hey guys :wave:t3:

Well, I’d like to add an option PT/EN, but I didn’t find a way to use GOOGLETRANSLATE with arrayformula

In my app people can add a text, and for this, gotta translate in each row…

Any way to do it?

3 Likes

My first two thoughts are combining them in a text join then split them back later in the same single formula.

Another thought is to use a script, I have written one recently that would be great for you. I’ll share it tomorrow when I’m up.

2 Likes

Even tho, I didnt find a way to do it with arrayformula

I’ll be greatful! :relaxed:

2 Likes

Hi brother, here is the Sheet so you can test.

The script:

  function Translate(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //checks that we're on the correct sheet.
  if(sheet.getSheetName() == 'Translate') { 
    var active_cell = sheet.getActiveCell();
    var row = active_cell.getRowIndex();
    if(row > 1) {
    var PortugueseCell = sheet.getRange(row,2);
    var VietnameseCell = sheet.getRange(row,3);
    PortugueseCell.setFormula("=GOOGLETRANSLATE(A"+row+",\"en\",\"pt\"\)");
    //sheet.getRange("B"+row).setValue(sheet.getRange("B"+row).getValue());
    VietnameseCell.setFormula("=GOOGLETRANSLATE(A"+row+",\"en\",\"vi\"\)");
    //sheet.getRange("C"+row).setValue(sheet.getRange("C"+row).getValue());
    }
  }
}

image

When you input something in A, it will translate to B and C and the end result is a text, not a formula so it won’t run all over again when you refresh!

3 Likes

Thank you so much man!!

I just wonder if it will count as a GS edit/reload in glide :thinking:

1 Like

As it inputs new information I think that’s a yes…

Ok…while I have a few users thats ok, but my worry when it gets more n more

1 Like

I would say it happens instantly after you input the original text so probably Glide can batch it in one update to the app so hopefully it doesn’t result in more reloads. I saw Jeff or David talking about this.

1 Like

Yes, I’ll try then… thanks again bro!!

1 Like

Ping me when you need help bro, and take a rest as well, it’s late over there haha.

1 Like

@ThinhDinh take a look in this formula :open_mouth:

4 Likes

Smart use of textjoin, thanks bro! :heart_eyes:

2 Likes