How to use a cell value to set a target sheet as a variable

0
votes

I have a test sheet where I am using an onEdit script to copy a row of data to a different tab, and delete the row from the source. It works fine, but I would like a way for the target sheet to be a variable based on a cell value in the row.

/* This script will send the the student data from source sheet to target sheet when checkbox is true in ColA
*/


function onEdit(event) {
  const ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var eventSheet = event.source.getSheetByName("sheet1");
  const sourceSheet = ss.getSheetByName("sheet1");
  const targetSheet = ss.getSheetByName("sheet2");
  var eventRange = event.source.getActiveRange();
  //let destName = eventSheet.getRange(i,eventRange.getLastColumn()).getValue();


if(eventSheet.getName() == sourceSheet.getName()) {

  if (eventSheet.getName() == sourceSheet.getName() && eventRange.getColumn() == 1 && eventRange.getValue() == true) { //if a change happens on the source sheet & that change is in column 1 & the column 1 value becomes true

    var rowNum = eventRange.getRow(); //get the row number of the checked row
    var numColumns = eventSheet.getLastColumn(); //get the number of columns in the source sheet
    const row = eventSheet.getRange(rowNum, 2, 1, numColumns).getValues(); //get all the data in the row of the checked row

    var result = ui.alert( //warn the user they are about to save a record
      'Are you sure you this student is ready to export? This will delete this row',
      ui.ButtonSet.YES_NO);

    if (result == ui.Button.YES) { //if the user clicks yes */
      targetSheet.appendRow(row[0]); //add the row to the target sheet
      sourceSheet.deleteRow(rowNum); //delete the row from the source sheet
    } else { //if the user clicks no
      const checkBox = sourceSheet.getRange(rowNum, 1); //get the selected checkbox
      checkBox.setValue(''); //clear the checkbox
    } 
  }


}
}

What I would like is a way for the targetSheet to be a variable gotten from the second column of data which matches a sheet name. So if a row of data has 'bob' in column b, the data will be copied to bob, if it is 'john' it is copied to john, etc. Right now it works to send data from sheet1 to sheet2, works great, but will be more useful to copy to a sheet based on the sheet name. How can I make targetSheet a variable? Example sheet: https://docs.google.com/spreadsheets/d/17Jo-emwVG199x19uiJUsX3q0xK9x-JIfUW45wGrTTIM/edit?usp=sharing

1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

I believe your goal as follows.

  • When the checkbox of the column "A" of "sheet1" is checked, you want to retrieve the values of columns "B" to "E" and the value of columns "C" to "E" to appended to the sheet of sheet name retrieve from the column "B".

Modification points:

  • In your script, I think that the event object can be used.
  • eventSheet.getName() == sourceSheet.getName() is used 2 times. In this case, this can be used 1 time.
  • In order to confirm the value of checkbox, you can use isChecked().
  • In order to uncheck the checkbox, you can use uncheck().

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

Modified script:

function onEdit(event) {
  const sourceSheet = "sheet1";
  const eventRange = event.range;
  const eventSheet = eventRange.getSheet();
  if (eventSheet.getSheetName() == sourceSheet && eventRange.columnStart == 1 && eventRange.isChecked()) {
    const ui = SpreadsheetApp.getUi();
    const result = ui.alert('Are you sure you this student is ready to export? This will delete this row', ui.ButtonSet.YES_NO);
    if (result == ui.Button.YES) {
      const [name, ...value] = eventRange.offset(0, 1, 1, 4).getValues()[0];
      const targetSheet = event.source.getSheetByName(name);
      if (targetSheet) {
        targetSheet.appendRow(value);
        eventSheet.deleteRow(eventRange.rowStart);
      } else {
        throw new Error("No sheet.");
      }
    } else {
      eventRange.uncheck();
    }
  }
}
  • When you use this script, please check the checkbox of the column "A" of "sheet1". By this, the values are moved to the specific sheet retrieved from the column "B". If you directly run the function of onEdit with the script editor, an error occurs. Please be careful this.

Note:

  • In this modified script, I tested it using your sample Spreadsheet. So when the structure of your sample Spreadsheet is different from your actual situation, the script might not be able to be used. So please be careful this.

References: