Thank you in advance!

Goal/Help Request:

I'd like to use the array I have in my code (which I think is an object literal?) and if a exact match occurs from the data set (Food column), it inserts the paired answer from the array into the "Pairing" column.

Issue/Thoughts:

I think I need to use Object.values()& Object.keys(). I'm having a difficult time trying to get my function to run through the data and compare....I thought a for statement would work but it doesn't seem so and my forEach code says it's not a function and I'm a bit lost on how to have it go through each corresponding row like you would do with i .

Sheet before script:

Desired output:

Data:

CODE:

    function apples() {
      sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
          const combo = {
                          'apple toast':'juice',
                          'apple cheese':'water',
                          'orange':'milk',
                          'orange peel':'OJ'
                          }
    const food = sheet.getRange("A2:A5").getValues().flat();
    const foodrownum = sheet.getDataRange().getNumRows();
    const pairing = sheet.getRange("B2:B5");

    var keys = Object.keys(combo)

    combo.forEach(function(item,index,array){
          if(item===food){
              sheet.getRange(2,2,foodrownum).setValue(Object.values(combo));
          }
    });

    }//end of function

References:

1. how to access individual elements of a javascript object literal?
2. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide
3. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Grammar_and_types#object_literals

CodePudding user response：

You can try this:

function apples() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const combo = {
    'apple toast': 'juice',
    'apple cheese': 'water',
    'orange': 'milk',
    'orange peel': 'OJ'
  }
  const food = sheet.getRange("A2:A8").getValues().flat();
  const pairing = sheet.getRange("B2:B8");

  const entries = Object.entries(combo);

  for (let i = 0; i < food.length; i  ) {
    if (food[i] in combo) {
      pairing.getCell(i   1, 1).setValue(combo[food[i]]) //Adds the value to each row depending on the key value
    }
    else {
      pairing.getCell(i   1, 1).setValue('Not found') //This since, both combo and your sheet elements are not the same size
    }
  }
}

As to why you are getting the error, basically the way it was implemented was not correct as per documentation

CodePudding user response：

Modification points:

• When setValue is used in a loop, the process cost becomes high. Ref
• combo is JSON object. In this case, you cannot directly use forEach.
• In the case of sheet.getRange(2,2,foodrownum).setValue(Object.values(combo)), when your value of combo is used, only juice is put to the column "B".
• I thought that in your situation, the method for creating the output values from the values of column "A" using JSON object of combo might be suitable.

When these points are reflected to a sample script, it becomes as follows.

Sample script:

function apples() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const combo = {
    'apple toast': 'juice',
    'apple cheese': 'water',
    'orange': 'milk',
    'orange peel': 'OJ'
  };
  const range = sheet.getRange("A2:A"   sheet.getLastRow());
  const values = range.getValues().map(([a]) => [combo[a.trim()] || null]);
  range.offset(0, 1).setValues(values);
}

• When this script is run, using combo and the values of the column "A", the new values are put to the column "B".

References:

• map()
• getValues()
• setValues(values)