Sample sheet

This is the code I have now, and what I want to do is also bring in all data from C where text does not include '250p' in col A.

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)
}

I tried doing: if (!type.toLowerCase().includes('250p')) { if (type.whenTextDoesNotContain('250p')) {

But on both occasions I get that, that is not a function.

CodePudding user response：

I believe your goal is as follows.

• Your Spreadsheet has the values in the columns "A" and "C".
• You want to check the column "A". When the value of column "A" doesn't include the text of 250p, you want to copy the values from the column "C" to the column "A" of the destination sheet. In this case, you want to split the values by \n.

Modification points:

• In your script, in order to retrieve the values from the columns "A" and "C", I thought that const range = 'A2:B' should be const range = 'A2:C' sheetIn.getLastRow();, and also const [type, name] = arr is const [type, , name] = arr.
• In order to retrieve the rows that 250p is not included in the column "A", I modified your if statement to if (!type.toLowerCase().includes('250p')) {.

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

Modified script:

const sS = SpreadsheetApp.getActiveSpreadsheet();
function grabData() {
  const sheetIn = sS.getSheetByName('data');
  const sheetOut = sS.getSheetByName('Desired Outcome');
  const range = 'A2:C'   sheetIn.getLastRow();
  const values = sheetIn.getRange(range).getValues();
  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]);
  sheetOut
    .getRange(sheetOut.getLastRow()   1, 1, parsedValues.length)
    .setValues(parsedValues);
}

• If you want to retrieve the rows that 250p is included in the column "A", please modify if (!type.toLowerCase().includes('250p')) { to if (type.toLowerCase().includes('250p')) {.

Note:

• In this modified script, your provided Spreadsheet is used. So, when you change the Spreadsheet, this modified script might not be able to be used. Please be careful about this.