1

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?

  • For a starter, you have fromsheetname there, but there is no parameter called that way. Should it be sheetname? – user79865 Jan 20 '16 at 22:33
  • Yes @Norma, it should be sheetname. I have edited my post. The original has the name matching the parameter. – DocDJ Jan 21 '16 at 1:08
  • You also have columnstart instead of colstart, and lack the keyword "function" before myfcn. Also, you say you want to get "values", but the function uses getFormula instead of getValue. – user79865 Jan 21 '16 at 1:34
  • Instead of return(x); the correct syntax is return x;. See developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/… – Rubén Jan 21 '16 at 2:47
  • Are you saying that you want to create a .csv file that automatically update each time that you change a value in your spreadsheet? – Rubén Jan 22 '16 at 14:41
2

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.

  • HI @Norma,the function call looks like this: =myfcn("lmaster",ROW(D10),1,COLUMN(D10),1) which allows me to have the cell reference updated during copy & paste of the formula, while passing the actual reference, instead of the contents of that cell. Also, I removed the () around the x and that didn't change anything. And as you can see, I DO use the getRange and getFormula, but it isn't updating. – DocDJ Jan 21 '16 at 16:34
  • I added: ScriptApp.newTrigger("myfcn") .forSpreadsheet(sheet) .onOpen() .onChange() .onEdit(); and got this error message. You do not have permission to call forSpreadsheet – DocDJ Jan 21 '16 at 16:49
  • My purpose of all this is this: I have a sheet that does computations with named values (codes). I need to DISPLAY the named values in each cell where they are used, so they can be saved AS TEXT along with their computed value. This allows my users to see the codes that were applied, in a separate file without access to the spreadsheet itself. – DocDJ Jan 21 '16 at 16:55
  • I guess i wasn't clear. In sheet1 I have formulas like =xx+yy. Several cells in sheet1 have such formulas. In sheet 2, I want to reference those cells (I know the specific cells) and display the value (as a number) AND the actual formula (as text) in cells of sheet 2. Later, I will copy ALL of sheet 2 and export it as an Excel sheet, then save it as a .csv file. That file will then be used to display the values and codes from a web-based program. I could use formula view, then copy & paste the formulas and values into Excel, but I want to automate as much of this as I can. – DocDJ Jan 21 '16 at 19:08
  • I apologize for my lack of clarity. I have re-written my original posting in hopes that you can see what I am trying to do. – DocDJ Jan 22 '16 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.