CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Kevinbeijing
Kevinbeijing

Reputation: 3

Google Sheets Script Help Request: Copy check boxed rows from multiple sheets to new sheet

Full disclosure: I have zero programming experience, but I'm a quick study.

I am trying to be able to copy individual rows from a number of different sheets to be copied to one specific sheet. I spent some time looking at previous questions/answers on this (great) site, and I was able to piece together a script that seems to do PART of what I'm looking for.

I can see where in the script I could/should change to get the functionality I'm looking for -- BUT I don't know what to put in the code to actually do what I'm hoping to do. I come to this community humbly asking for help/guidance.

Here's the script that I pieced together and a link to the example I've been working with:

function onEdit(event) {
 // assumes source data in sheet named ABC
 // target sheet of copy to named Interdisciplinary
 // getColumn with check-boxes is currently set to colu 15 or O
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var s = event.source.getActiveSheet();
 var r = event.source.getActiveRange();

 if(s.getName() == "ABC" && r.getColumn() == 15 && r.getValue() == true) {
   var row = r.getRow();
   var numColumns = s.getLastColumn();
   var targetSheet = ss.getSheetByName("Interdisciplinary");
   var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
   s.getRange(row, 1, 1, numColumns).copyTo(target);
  } else if(s.getName() == "Interdisciplinary" && r.getColumn() == 4 && r.getValue() == false) {
 }
}

I know that I'm identifying a specific sheet to pull data from (ABC), what might I put there to pull from any sheet where a checkbox has been clicked?

I also know that I'm having the rows copied to a row that is some defined place away from the LastRow. What might I be able to put there to have the row copied either a row w/in my semester headings OR to have the copied to the top of the new sheet?

Finally, I'd truly appreciate any other comments, feedback, guidance that the community might have for my request.

Thanks much! Kd

Upvotes: 0

Views: 168

Answers (1)

Diego
Diego

Reputation: 9571

Your code works. The problem is that the code in your spreadsheet is looking at column 21, not column 15.

Also, I suggest that you review the properties available to you in the event object. You should use them instead of the first three declarations you wrote. You can see the change in my version below.

function onEdit(event) {
  var sheets =  ["ABC", "DEF", "GHI", "JKL", "MNO"]; // Sheet names that will trigger execution. Case-sensitive.

  // target sheet of copy to named Interdisciplinary
  // getColumn with check-boxes is currently set to colu 15 or O
  var ss = event.source;
  var s = event.range.getSheet();
  var r = event.range;

  if (sheets.indexOf(s.getName()) != -1 && r.getColumn() == 15 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Interdisciplinary");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
  } else if(s.getName() == "Interdisciplinary" && r.getColumn() == 4 && r.getValue() == false) {

  }
}

EDIT: I've modified the code to show you how to allow triggering from multiple sheets.

Upvotes: 1

Related Questions