It's a common issue, so a few similar questions have been asked on stackoverflow already:
function recalculates every time i open the sheet, I only want it to recalculate on edit
how to make a custom function NOT recalculate in Google Sheet
Stop a custom function from recalculating every time the spreadsheet is open?
The solutions suggested have inspired to try to code this specific solution, but I'm getting stuck. Imagine we have the following in a Google Sheets:
| Translation | Recalculate? | Text |
|---|---|---|
| =TRANSLATE($C2, "es", "en") | NO | House |
| =TRANSLATE($C3, "es", "en") | YES | Cat |
On the Apps Script code I'm trying to get the value on the column "Recalculate?" (the next one). Is it possible from the parameter "text"? The feeling is that I'm missing something basic that is driving me crazy. This is one of my attempts:
function TRANSLATE(text, sourceLang, targetLang) {
row = text.getRow;
column = text.getColumn;
var currentSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if (currentSheet.getRange(row,column-1) == "YES"){
DEEPL(text, sourceLang, targetLang);
}
}
I'm getting different errors like Exception: The parameters (null,number) don't match the method signature for SpreadsheetApp.Sheet.getRange. (line 187).. I also tried with row = text.getRow();, just to get the error: TypeError: text.getRow is not a function (line 183).
Please note that I may just add a new parameter to the function: function TRANSLATE(text, recalculate, sourceLang, targetLang), but then:
I would had to modify not only the code, but also the sheets
I would learn nothing! And one of the reasons of stackoverflow is learning from each others. Isn't? :)
I observe that the column Recalculate is at the left of the column Text (so that's why I did column-1), but it also at the right of where the function is being executed, so it could be another way around if it's possible to get the position where the function =TRANSLATE($C2, "es", "en") is being executed.
