Having problems with custom function

Question

I am converting some spreadsheets from Excel to Google Sheets, and there is a function I am trying to do which gets the code and apply the function. For example I have a list of codes in a column and the next 5 columns with texts. I want to type =myFunction("code") and I would like to return the value with this formula =vlookup(code;A1:F30;3;0), that would return the column 3 with the row the code is.

I've tried these:

function myFunc(code) {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cell = sheet.getRange(getCell);
  var test = cell.setFormula('=VLOOKUP('+code+';a1:b10;2;0)')
  return test;
}

it says I don't have permission to call setFunction,

function gettext(code) {
  var func = '=VLOOKUP("'+ code +'";\'VA-Texte\'!A1:I383;\'VA-Texte\'!E1;0)'; 
  return func;
}

it prints the exact code I need, but does not act like a formula.

Thank you Ruben. How can I make a function to work like a VLOOKUP, where I type "=myFunction("3-123")" and it will return the column and the row corresponding to that code? — Alexandre, Jul 08 '19 at 19:30
The question is too broad. Have you already read https://developers.google.com/apps-script/guides/sheets/functions? — Rubén, Jul 08 '19 at 19:32
Yes I have, I edited the post with some options that I tried, but with no luck. — Alexandre, Jul 08 '19 at 19:39
Google Apps Script can't evaluate Google Sheets formulas. On Google Apps Script code use JavaScript instead Google Sheets formulas. — Rubén, Jul 08 '19 at 19:44
Thank you for your answer. do you know where I can find the documentation about javascript for gsheets? I know a bit about Javascript but I am struggling on putting it into the SpreadSheet. — Alexandre, Jul 08 '19 at 19:52
For JavaScript documentation try https://developer.mozilla.org/en-US/docs/Web/JavaScript also read [Which Edition of ECMA-262 Does Google Apps Script Support?](https://stackoverflow.com/q/17252409/1595451) — Rubén, Jul 08 '19 at 19:57
@Alexandre These topics appear relevant to your question; arguably your question is a duplicate.[Is there a way to emulate Vlookup in Google Script?](https://webapps.stackexchange.com/q/123670/196152) and [Writing google Javascript similar to vlookup](https://stackoverflow.com/q/10838294/1330560) — Tedinoz, Jul 10 '19 at 22:31

Tedinoz · Accepted Answer · 2019-07-12T13:44:36.117

Your code can't be evaluated because we don't know the value of getCell.

In the code below "lookup" is a named range. The range is Cell E4 which contains the value "ghi", which is the search term.

function test01(){

 var abc = "lookup";  
  so5693959103(abc);

}
function so5693959103(abc) {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "56939591";
  var sheet = ss.getSheetByName(sheetname);

  // Lookup value is the named range = lookup which is found in E4
  var vlookupformula03 = "=VLOOKUP("+abc+",A1:B10,2,0)";

  //insert formula into cell E8
  var targetRange03 = sheet.getRange("E10");

  targetRange03.setFormula(vlookupformula03);
  Logger.log("DEBUG: result03 = "+targetRange03.getValue());

}

Vlookup screenshot

Edit

This revision will work for a function value of =mylookup("xxx"), where 'xxx' is the lookup value. In the example below, the lookup value is 'ghi'

/*
Imitates the Vlookup function. Receives:
search - The desired value to look for in the column.
Once the cell of the [search] has been found, the returned parameter would be the value of the cell which is 1 cell to the right of the found cell.
*/
function mylookup(search) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "56939591";
  var sheet = ss.getSheetByName(sheetname);
  var thevalue = search;
  var lastRow=sheet.getLastRow();
  var data=sheet.getRange(1,1,lastRow,2).getValues();

  for(i=0;i<data.length;++i){
    if (data[i][0]==thevalue){
      return data[i][1];
    }
  }
}

Mylookup function

Thank you for your answer Tedinoz, but what I was looking for is when you type in a cell,following your example, '=myFunction(ghi)' it returns '3'. In your example the formula is static on cell 'E10'. Thank you though. — Alexandre, Jul 10 '19 at 15:08
I can give you `=myFunction("ghi")`, but not `=myFunction(ghi)`. — Tedinoz, Jul 10 '19 at 22:27
Perfect! It worked like a Charm. I've been out of that project for a while that's why I didn't try it before but it worked just as I wanted to. Thank you very much Tedinoz for your help. — Alexandre, Aug 17 '19 at 16:46

Having problems with custom function

1 Answers1