Calling an API in Google Sheets only when changed have been applied to a cell

Question

I'm Thomas and pretty new to appscript. I managed to use it in order to translate cells in a google sheet using DeepL API. I did it thanks to this post : https://webapps.stackexchange.com/questions/149026/integrate-deepl-with-google-sheets But I have now an other problem. The data is refreshed each time the sheet is opened hence consuming a lot of translation characters. The 500000 characters/month limit has been reached in only 2 days of using this sheet... And we were only 2 using it. Is there any way of refreshing a cell only when the input content has been changed ? Let's say content input is in cell A1 and translated content - output- is in cell A2. I want A2 to be refreshed only when A1 has changed. Thanks a lot to all for your help !

Answer 1

We finaly found a solution, thanks to my IT guy at work. Here it is :

function onEdit2(e) {
//  Vérifie que l'onglet est à traiter
    if (e.source.getActiveSheet().getName() !== 'Spare Parts Translation') return;
//  Si valeur colonne 3 modifiée, on modifie les traductions
    if (e.range.columnStart == 3) {
//    Texte à traduire
      var texte_fr = e.source.getActiveSheet().getRange(e.range.rowStart, e.range.columnStart).getValue();
//    Ecrit texte traduit
      e.source.getActiveSheet().getRange(e.range.rowStart, e.range.columnStart + 1).setValue(traduit(texte_fr,"en"));
      e.source.getActiveSheet().getRange(e.range.rowStart, e.range.columnStart + 2).setValue(traduit(texte_fr,"de"));
      e.source.getActiveSheet().getRange(e.range.rowStart, e.range.columnStart + 3).setValue(traduit(texte_fr,"es"));
      e.source.getActiveSheet().getRange(e.range.rowStart, e.range.columnStart + 4).setValue(traduit(texte_fr,"it"));
    }
}

function traduit(texte,lang) {
  var response = UrlFetchApp.fetch("https://api-free.deepl.com/v2/translate?auth_key=*ENTER YOUR API KEY HERE*&text=" + texte + "&target_lang=" + lang + "&source_lang=fr", {muteHttpExceptions: true});
  var json = response.getContentText();
  var data = JSON.parse(json);
  if (data["message"] == "Quota Exceeded") {
    var texte = data;
  }else{
    var texte = data["translations"][0]["text"];
  }
  return texte;
}

Answer 2

I am not sure how to disable the refresh on opening the sheet, but there may be another way to achieve what you want:

After the DeepL translation runs and the cells are filled with the translated text, you could save the results to those cells by copying and pasting the values over the top. That is:

• select the cells you want to save
• Copy (Ctrl-C)
• Paste special -> Values only (Ctrl-Shift-V)