I'm trying to create a centralised list of ingredients that are brought into a warehouse. I know the Name, Volume & Cost of each of those ingredients and have detailed them in a master spreadsheet that will be updated with new ingredients and amended prices as time goes on.

I also have a bunch of recipes spreadsheets that use those ingredients. What I'd like to do is have those recipes spreadsheets reference the master spreadsheet to pull the cost value in the cell and volume value in the adjacent cell when the name of the ingredient is mentioned in plain English and then have those values updated when the master spreadsheet is amended.

function dataLookUp(item) {
    var item = item.toLowerCase() // convert to lower case 
        .trim()                     // remove spaces at the start and at end
        .replace(/  /g, ' ');       // replaces double spaces with single spaces
    
      var ss = SpreadsheetApp.openById("Master Cost ID").getSheetByName("Cost");
      var sheets = ss.getsheets();
      var data = sheet.getDataRange().getValues();

    function get_value(item) {
       
        var texts = ['GNS@60'];
        if (texts.includes(item))      return 0.64;
    }
    function get_volume(item) {
       
        var texts = ['GNS@60'];
        if (texts.includes(item))      return 1000; //in adjacent cell
    }
    }

I have tried a few methods and I'm struggling to get this off the ground. I'm trying to express what I want to do in garbage code, apologies if my code doesn't make sense. I don't particularly want to use IF because then every time I add a new ingredient, I'll have to change the script with hundreds of entries. I'm at a bit of a loss where to go.

EDIT - As requested, a sample spreadsheet here Thanks for helping out with this code:

/**
 * This fuction determines whether the ingredient is in the database & returns it's price point and breaks
 * @constructor
 * @customfunction
 *  */ 

function getCost(itemname){
  const ss1 = SpreadsheetApp.getActive();
  const ssh = ss1.getSheetByName('Cost');
  let tf = ssh.getRange('A1:A'  ssh.getLastRow()).createTextFinder(itemname).findNext();
  return ssh.getRange(tf.getRow(),3).getValue();
}
function getVol(itemname){
  const ss1 = SpreadsheetApp.getActive();
  const ssh = ss1.getSheetByName('Cost');
  let tf = ssh.getRange('A1:A'  ssh.getLastRow()).createTextFinder(itemname).findNext();
  return ssh.getRange(tf.getRow(),2).getValue();
}

This works great for a cost sheet that is in the same spreadsheet, but what I'm wanting to achieve is scraping values from an external spreadsheet (as in a different file) that is updated separately. This way if 20 recipes use the same ingredient and the price changes, I can just update the master cost spreadsheet instead of individually changing each file. I'm running into a problem when trying to point the script towards an external spreadsheet via SpreadsheetApp.openById which pings an error:

Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets

I've read that .openById doesn't work anymore for what I'm trying to do. Is there another method to do this?

CodePudding user response：

Return item cost

function getCost(itemname){
  const ss1 = SpreadsheetApp.getActive();
  const ssh = ss1.getSheetByName('Sheet1');
  let tf = sh.createTextFinder(itemname).findNext();
  return ssh.getRange(tf.getRow(),3).getValue();
}

or

function getCost(itemname){
  const ss1 = SpreadsheetApp.getActive();
  const ssh = ss1.getSheetByName('Sheet1');
  let tf = sh.getRange('A1:A'  ssh.getLastRow()).createTextFinder(itemname).findNext();
  return ssh.getRange(tf.getRow(),3).getValue();
}

CodePudding user response：

Try this:

Since now you are getting a permission error when opening the external script you could try the following:

• Try adding the specific scopes in the appscript.json file manifest, to do this follow this documentation.

• If the above still throws the same message find your project and remove all access from your account settings, once done try giving permissions to the script again when running the code.