How to write a value to the last row in a column based on the sum of two cells-CodePudding

I have a sheet in which I want to add an x to the next empty row in a column based on an if statement. In Cell B1 and C1 I have a randbetween function outputting a number between 1 and 6. If the sum of these two random numbers is 2, add an x to the next empty row in column D, if sum is 3 add the x to column E, if sum is 4 add the x to column F, and so on. The sums will be between 2 and 12, so x's will be added to columns D-N based on the random sum. The randbetween function on the sheet is triggered by a checkbox in cell A1. So I have the following code that works to add an X to column D when the checkbox is checked. I am not sure how to write the if statement to output the x to the correct column based on the sum of the two 'dice'.

This code works to add an x to the columns if I change the last line to a different column. I would like to add an if statement that would write the x to the last row of the correct column based on the sum.

function onEdit(e) {
  if (e.oldValue !== "false" || e.value !== "TRUE") return;
  const cell = e.range;
  if (cell.rowStart !== 1) return;
  if (cell.columnStart !== 1) return;
  addX();
  cell.setValue(false);
}

function addX() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('dice roller auto');

  const dVals = ss.getRange("D1:D").getValues();
  const dLast = dVals.filter(String).length;
  const dCell = sheet.getRange('D'   (dLast   1));

  const eVals = ss.getRange("E1:E").getValues();
  const eLast = eVals.filter(String).length;
  const eCell = sheet.getRange('E'   (eLast   1));

  const fVals = ss.getRange("F1:F").getValues();
  const fLast = fVals.filter(String).length;
  const fCell = sheet.getRange('F'   (fLast   1));

  const gVals = ss.getRange("G1:G").getValues();
  const gLast = gVals.filter(String).length;
  const gCell = sheet.getRange('G'   (gLast   1));

  const hVals = ss.getRange("H1:H").getValues();
  const hLast = hVals.filter(String).length;
  const hCell = sheet.getRange(('H'   (hLast   1)));

  const iVals = ss.getRange("I1:I").getValues();
  const iLast = iVals.filter(String).length;
  const iCell = sheet.getRange(('I'   (iLast   1)));

  const jVals = ss.getRange("J1:J").getValues();
  const jLast = jVals.filter(String).length;
  const jCell = sheet.getRange(('J'   (jLast   1)));

  const kVals = ss.getRange("K1:K").getValues();
  const kLast = kVals.filter(String).length;
  const kCell = sheet.getRange(('K'   (kLast   1)));

  const lVals = ss.getRange("L1:L").getValues();
  const lLast = lVals.filter(String).length;
  const lCell = sheet.getRange(('L'   (lLast   1)));

  const mVals = ss.getRange("M1:M").getValues();
  const mLast = mVals.filter(String).length;
  const mCell = sheet.getRange(('M'   (mLast   1)));

  const nVals = ss.getRange("N1:N").getValues();
  const nLast = nVals.filter(String).length;
  const nCell = sheet.getRange(('N'   (nLast   1)));

  nCell.setValue('x');

}

Here is the sheet (with edit rights) I am trying to build, it is a way to visualize what happens when you roll two dice. It would be cool if an apps script could write the x's as soon as the checkbox is checked or unchecked. The way it works now on the manual sheet students will have to manually add the x's and as they add an x, the dice are rolled.

After:

CodePudding user response：

If you put this formula off to the right in some cell it might make things a lot easier. It will return the address of where the entry should be placed after a roll.

=ADDRESS(counta(index(D:N,,MATCH(B1 C1,D1:N1,0))) 1,MATCH(B1 C1,$D$1:$N$1,0) COLUMN($C$1))

I'd name the cell something like entryAddress then a script something like this...

From there, you could just include something this in your procedure:

function onEdit(e) {
  if (e.oldValue !== "false" || e.value !== "TRUE") return;
  const cell = e.range;

  var zAddress = ss.getRange("entryAddress").getValue();
  ss.getRange(zAddress).setValue("x");


  cell.setValue(false);
}