I've done extensive search for this, but none of them seems to work. They all just give me a blank sheet.

So it would be like: IF (DATA!A1:A contains text "250p" then run function EXTRACT).

This is the code I have as of now:

//this extract works fine but I just need this to work for only those with value 250 in Col A//

function EXTRACT() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').setFormula('=EXTRACTDATA(DATA!A1:A)');

}

function IF250() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('DATA');
  var range = sheet.getRange('DATA!A1:A');
  var values = range.getValues();

  if (values[i] == "250g") {
    EXTRACT();

Better yet, If I can have the data set in 2 separate sheets. The 250s in one sheet & 500s in one sheet. But this is not necessary.

CodePudding user response：

Try changing:

var values = range.getValues();

to

var values = range.getDisplayValues()

As this will read the value that is shown. Try logging the values with both to see why! (Blank)

You are also not currently iterating, or looping, your values.

If you're just looking to see if the column contains a cell containing the value 250p, try:

function IF250() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`DATA`)

  const valueExists = sheet.getRange(`A1:A`)
                           .getDisplayValues()
                           .filter(String)
                           .some(row => row.includes(`250p`))

  if (valueExists) EXTRACT()

}

Commented:

function IF250() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`DATA`)

  const valueExists = sheet.getRange(`A1:A`)
                           .getDisplayValues()
                           // Remove empty cells (not strictly necessary)
                           .filter(String)
                           // If the values include a row containing `250p` return true.
                           .some(row => row.includes(`250p`)) 
  // If valueExists returns true:
  if (valueExists) EXTRACT()

}

CodePudding user response：

After reviewing your sheet, this is a possible solution

Code.gs

const sS = SpreadsheetApp.getActiveSpreadsheet()
function grabData() {
  const sheetIn = sS.getSheetByName('data')
  const sheetOut = sS.getSheetByName('Desired Outcome')
  const range = 'A2:B'
  /* Grab all the data from columns A and B and filter it */
  const values = sheetIn.getRange(range).getValues().filter(n => n[0])
  /* Retrieve only the names if it containes 250p */
  /* In format [[a], [b], ...] */
  const parsedValues = values.map((arr) => {
    const [type, name] = arr
    if (type.toLowerCase().includes('250p')) {
      return name.split('\n')
    }
  })
    .filter(n => n)
    .flat()
    .map(n => [n])
  /* Add the values to the Desired Outcome Sheet */
  sheetOut
    .getRange(sheetOut.getLastRow()   1, 1, parsedValues.length)
    .setValues(parsedValues)
}