I need some help adapting this map so that instead of emptying the row, to subtract 1 from the values for each row on columns 0,2,4,6.

*UPDATED for clarification:

I am looking for the function to work as follows: if a name in SpreadsheetA matches any in SpreadsheetB, subtract 1 from the corresponding grade column of a name, such that in any given row

if col1 equals any value from the SpreadsheetB range, -1 from col0
if col3 equals any value from the SpreadsheetB range, -1 from col2
if col5 equals any value from the SpreadsheetB range, -1 from col4
if col7 equals any value from the SpreadsheetB range, -1 from col6.

Perhaps I could just create separate maps for each of the above? My apologies for any confusion. SpreadsheetAstart SpreadsheetBstart SpreadsheetBfinish

function removeNotReturning() {
var ssNR = SpreadsheetApp.openById(SpeadsheetA).getSheetByName("Not returning").getRange("A2:A10");
var ssNRv = ssNR.getValues().flat();
var ssCP = SpreadsheetApp.openById(SpreadsheetB).getSheetByName("Master").getRange("B2:I10");
var ssCPv = ssCP.getValues();
ssCPv = ssCPv.map(nRow => {
var nameExists = [nRow[1],nRow[3],nRow[5],nRow[7]].some(nName => ssNRv.includes(nName));
if (nameExists) nRow.fill("");
return nRow;
});
ssCP.setValues(ssCPv);
}

CodePudding user response：

In your situation, how about the following modification?

From:

if (nameExists) nRow.fill("");

To:

if (nameExists) [0, 2, 4, 6].forEach(i => nRow[i] -= 1);

• By this modification, when nameExists is true, 1 is reduced from the element of [0, 2, 4, 6] instead of nRow.fill("").

• In this case, if the cell value is not the number, #NUM! is returned. Please be careful about this.

• If your cells include the string, please modify as follows.

    if (nameExists) [0, 2, 4, 6].forEach(i => nRow[i] = isNaN(nRow[i]) ? nRow[i] : nRow[i] - 1);
  

Added:

From your updated question, how about the following sample script?

Sample script:

function removeNotReturning() {
  var srcSpreadsheetId = "###"; // Please set your source Spreadsheet ID.
  var srcSheetName = "Not returning";
  var dstSpreadsheetId = "###"; // Please set your destination Spreadsheet ID.
  var dstSheetName = "Master";

  var [srcSheet, dstSheet] = [[srcSpreadsheetId, srcSheetName], [dstSpreadsheetId, dstSheetName]].map(([id, name]) => SpreadsheetApp.openById(id).getSheetByName(name));
  var obj = srcSheet.getRange("A2:A"   srcSheet.getLastRow()).getValues().reduce((o, [a]) => (o[a] = true, o), {});
  var range = dstSheet.getRange("B2:I"   dstSheet.getLastRow());
  var values = range.getValues()
  values.forEach(r => r.forEach((c, j) => {
    if (obj[c]) r[j - 1] -= 1;
  }));
  range.setValues(values);
}