Google-sheets – Getting values from other sheets with a custom function

google sheetsgoogle-apps-scriptgoogle-sheets-custom-function

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?

Best Answer

Custom functions are re-run when their parameters change. For example, if you have =myfcn("sheetname", 1, 1, 1, 1) and change some of parameters here, the function will run again. But if you go and change the content of cell sheetname!A1, the function will not re-run, since there is no indication in the spreadsheet that its output depends on sheetname!A1.

Alternative

To get all formulas from a sheet into another sheet as text, use a script function like this:

function copyFormulas() {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() == 'Watched Sheet') {   
    var data = sheet.getDataRange();
    var target = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Formulas');
    target.getRange(1, 1, data.getHeight(), data.getWidth()).setValues(data.getFormulas());
  }   
}

The function takes formulas from 'Watched Sheet' and puts them into 'Formulas' sheet as text. This script can run periodically, or on every edit, or on every change (including adding rows, etc). See triggers.

Related Topic