Whenever there is a string in column G that equals any one of the strings in D12:K12 of the sheet titled Instructions I would like to delete that row.

function deleteRows() {
  const ss = SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();

  var host = ss.getRange("Instructions!D12:K12").getValues();
  var rang = sh.getRange(1,1,sh.getLastRow(), sh.getLastColumn());
  var valus = rang.getValues().filter(r => {if(r[6] != host) return true});
  rang.clearContent();
  sh.getRange(1,1,valus.length,valus[0].length).setValues(valus);
}

I believe there is an issue with how I wrote "r[6] != host" related to "host" being an array and not a single string but I am not sure what I am doing wrong. Can anyone offer some advice?

CodePudding user response：

This is a problem:

var valus = rang.getValues().filter(r => {if(r[6] != host) return true});

r[6] is a single cell in column 7 but host is a two dimensional array of values. So that doesn't make any sense.

You could conceivably mean something like this:

function deleteRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const ish = ss.getSheetByName("Instructions")
  var ivs = ish.getRange("D12:K12").getValues();
  var rg = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn());
  var vs = rg.getValues().filter((r, i) => r[6] != ivs[i][6]);
  rg.clearContent();
  sh.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
}

Sorry I require making all of my variables as small a possible because I hate to type anymore than I absolutely have to and I like my code to be as small as possible and still work with the editor format

CodePudding user response：

From your question of Whenever there is a string in column G that equals any one of the strings in D12:K12 of the sheet titled Instructions I would like to delete that row. and your showing script, I believe your goal is as follows.

• About the values of "Instructions!D12:K12", when one of those values is the same as the column "G" in the active sheet, you want to delete rows of the active sheet.

In this case, how about the following modification?

Modification points:

• In your script, host is a 2-dimensional array. Unfortunately, in this case, var valus = rang.getValues().filter(r => {if(r[6] != host) return true}) is not correctly worked.

• I thought that var rang = sh.getRange(1,1,sh.getLastRow(), sh.getLastColumn()); can be modified to var rang = sh.getDataRange();.

Modified script:

In this modification, when you activate the sheet and run this script, the search values are retrieved from "Instructions!D12:K12". And, using these values, column "G" of the active sheet is checked. And, the sheet is updated.

function deleteRows() {
  const ss = SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();

  var host = ss.getRange("Instructions!D12:K12").getValues()[0];
  var rang = sh.getDataRange();
  var valus = rang.getValues().filter(r => !host.includes(r[6]));
  rang.clearContent();
  sh.getRange(1, 1, valus.length, valus[0].length).setValues(valus);
}

References:

• includes()
• filter()