0

1st of all sorry for my poor english. trying to put with a macro a formula into a cell to find the corrispective value of 2nd column in row with "Banana" in first column with VLOOKUP.

simple spreadsheet with a module sheet linked

function searchBanana() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('A5').activate();


    spreadsheet.getCurrentCell().setFormula('=VLOOKUP("banana";fruit!A:D;2;FALSE)');
};

when I register the macro or I write the formula manually it works well, if I create the formula by macro I got error on formula (just on spreadsheet view, not in code view). If I copy-paste the formula generated by the macro into another cell it work.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Roberto Dalmo
  • 1
  • What is the error you get in your cell? And what is the data in `fruit!A:D`? – Benoît Wéry Aug 14 '19 at 11:52
  • 1
    Welcome. Would you please share a copy of your spreadsheet (excluding any private or confidential information), and include an example of a successful outcome for your problem. – Tedinoz Aug 14 '19 at 14:56
  • It seems to me that the issue is due limitations on google sheets to set cells values other that the one containing the formula. Running your code on debugger it does works, but the sheet lacks of permissions to do the change. https://developers.google.com/apps-script/guides/sheets/functions?hl=fr-FR https://stackoverflow.com/questions/15933019/google-script-setvalue-permission – Francisco Araujo Aug 14 '19 at 20:12

0 Answers0