Moving rows from multiple sheets with checkbox and script to one sheet

0
votes

With help from this great community I have made a script where I can move a row to another sheet by clicking a checkbox in column A. Using one script for copying text from cells in a row to another sheet using an icon in a row

I need more sheets with this checkbox functionality. Ie Ark2, Ark3 and so on. All should move data in row to sheet "Sick" without overwriting existing data.

From my existing script only one sheet "Ark1" is functioning and I'm sure it will overwrite in sheet "Sick" if I just copy the script and replace names on the sheets.

Here is the script I'm using.

function main(row) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Ark1');
  var [v, d] = s.getRange(`B${row}:C${row}`).getValues()[0];
  var s2 = ss.getSheetByName('Sick');
  var timeZone = ss.getSpreadsheetTimeZone();
  var t = Utilities.formatDate(new Date(), timeZone, 'HH:mm:ss dd-MM-yyyy');
  s2.getRange(`A${row}:C${row}`).setValues([[t,v,d]]);
}

function onEdit(e) {
  const range = e.range;
  if (range.getSheet().getSheetName() == "Ark1" && e.value == "TRUE") {
    main(range.rowStart);
    range.uncheck();
  }
}

An editable test version of my sheet can be found here: https://docs.google.com/spreadsheets/d/1z5TGWp3N002z0GNts4NRyspkjXcsN52rxPtx8Pu9D70/edit#gid=1278602995

As you can tell I'm a newbie here - but slowly progressing...

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

Answer:

You need to get the name of the edited Sheet rather than specifying "Ark1", and add it to the bottom of the sheet "Sick" using the getLastRow() method.

Code Changes:

function main(range) {
  var row = range.rowStart;
  var s = range.getSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var [v, d] = s.getRange(`B${row}:C${row}`).getValues()[0];
  var s2 = ss.getSheetByName('Sick');
  var timeZone = ss.getSpreadsheetTimeZone();
  var t = Utilities.formatDate(new Date(), timeZone, 'HH:mm:ss dd-MM-yyyy');
  s2.getRange(`A${s2.getLastRow() + 1}:C${s2.getLastRow() + 1}`).setValues([[t,v,d]]);
}

function onEdit(e) {
  const range = e.range;
  if (range.getSheet().getSheetName() != "Sick" && e.value == "TRUE") {
    main(range);
    range.uncheck();
  }
}

I hope this is helpful to you!

References: