Help with a function in apps scripts

[Mister D]

Hi there,

I've created a solution where I hide menu items based off a stock level. I'd like to make the solution more dynamic. Orignally I had been using a vlookup within a gsheet to identify the product ID. However I've moved that to the Menu ID look up now. I am automatically generating URL's for an API etc so all in all its getting better. The solution is a mixed bag of Zapier, Gsheets and Google apps scripts. It functions as expected, infact, better than I had hoped.

I am however stuck on an app script that I would like to apply to the solution.

function ChilliVegetariano() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();

 var s = ss.getSheetByName('Dataincoming');

 var r = s.getRange('A:A');

 var v = r.getValues();

 for(var i=v.length-1;i>=0;i--)

   if(v[0,i]=='Chili Vegetariano')

     s.deleteRow(i+1);

var url = showChilliVegetariano()};

What I would like to do is to rather than searching by name in "if(v[0,i]=='Chili Vegetariano')" I'd like to refrence a cell value so for example sheet name stocksheet and cell B2?



Any thoughts on how to achieve this?



I probably havent explaind this very well so please ask any questions that will help narow it down!

Thanks

[Jean-Luc Vanhulst]

If you do something like searchVar = s.getRange(‘X3’).getValue() ;

And then use searchVar where you now have the hardcoded value. And of course X3 would be wherever you put your value ?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d0af876a-508e-4994-b24a-2be7f2786014o%40googlegroups.com.

--

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

[Mister D]

Thanks, I've added the below and now I am just getting an infinite spoiner on execution, any Ideas about that?

I should also add that the sheet that I want to reference for the ID is not "dataincoming" rather it is "stocksheet". Hence the adding of sl  var and I also added in the sq var as a test. This did not work either. I've left it there to highlight the different pages.

function ChilliVegetariano() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sq = SpreadsheetApp.getActiveSpreadsheet();

  var s = ss.getSheetByName('Dataincoming');

  var r = s.getRange('A:A');

  var v = r.getValues();

  var sl = sq.getSheetByName('stocklevel');

  var searchVar = sl.getRange('B2').getValue() ;

  for(var i=v.length-1;i>=0;i--)

    if(v[0,i]= searchVar)

      s.deleteRow(i+1);

var url = showStockItem()};

[Mister D]

Actually, script just came back with

Error

Exception: You can't delete all the rows on the sheet.

[Mister D]

Cracked it.

My syntax was a bit off, triall and error saved the day!

function row2replenish() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s = ss.getSheetByName('Dataincoming');

  var r = s.getRange('A:A');

  var v = r.getValues();

  var sq = SpreadsheetApp.getActiveSpreadsheet();

  var sl = sq.getSheetByName('stocklevel');

  var searchVar = sl.getRange('B2').getValue() ;

  for(var i=v.length-1;i>=0;i--)

    if(v[0,i]== (searchVar))

      s.deleteRow(i+1);

var url = showStockItem()};

Thanks