if-statementgoogle-apps-scriptgoogle-sheetsdata-extraction

Google script - In the following code, instead of 'includes', how to do 'not includes'


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.


Solution

  • I believe your goal is as follows.

    Modification points:

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

    Note: