I've defined the following function, and use it in sheet1:
function myfcn (sheetname,rowstart,rows,columnstart,rows){
var sheet= SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
// sheetname is a parameter to my function
var range = sheet.getRange(rowstart,columnstart, 1,1);
var x=range.getFormula();
return x;
}
In sheet2, A1 has =100-B1
. D1 has =100-E1
. (and so on for 100's of formulas)
B1 has =x+y
, E1 has =q+p
.
x, y, q & p are named values. The 100-B1 is actually a MUCH more complex formula, but serves as an example.
My need is to create a .csv file (from sheet1) containing the VALUES (from sheet2) in A1, D1 etc. (using =A1
, etc.) and the FORMULAS in B1 and E1, etc. using myfcn
. The .csv file is then used as a pseudo-database where selected viewers (who are numbered) can see ONLY the row with their number. The viewers see a number and the formula (like x+y
) that created it. There is a SEPARATE file that has a simple list of the VALUES of x, y, q, p, etc.
But when I change the value in sheet2, it is not retrieved. Also, when I select the cell in sheet1 that uses the function and press F2, Google says it is an unknown function.
What am I doing wrong?
fromsheetname
there, but there is no parameter called that way. Should it besheetname
?getFormula
instead ofgetValue
.return(x);
the correct syntax isreturn x;
. See developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/…