GOOGLETRANSLATE with Arrayformula?

Ask for Help
1

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?

4 Likes
2

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
3

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

I’ll be greatful! :relaxed:

2 Likes
4

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!

4 Likes
5

Thank you so much man!!

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

1 Like
6

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

7

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

1 Like
8

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
9

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

1 Like
10

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

1 Like
11

@ThinhDinh take a look in this formula :open_mouth:

5 Likes
12

Smart use of textjoin, thanks bro! :heart_eyes:

2 Likes