Google sheets script if checkbox is checked than copy rows from one sheet to another

Question

I have a small problem. I want to copy data from Sheet1 to Sheet2 with a button provided the checkbox is checked. In Sheet2, find the last row and then use the next one. After the data has been transferred, the cell content in Sheet1 should be deleted and the checkbox should be reset.

Can anyone help me with this?

Here is a screenshot from my sheet

I found this Code:

function button1() {
  var ss=SpreadsheetApp.getActive();
  var sht1=ss.getSheetByName('tab1');
  var sht2=ss.getSheetByName('tab2');
  var lastRow=sht1.getLastRow();
  var rng=sht.getRange(1,1,lastRow,6);
  var rngA=rng.getValues();
  for(var i=0;i<rngA.length;i++)
  {
    if(rngA[i][2]==true)
    {
      sht2.getRange(i+1,4).setValue(rngA[i][0]);
      sht2.getRange(i+1,5).setValue(rngA[i][1]);
    }
  }
  SpreadsheetApp.flush();
}

But this code writes the data right next to the row. I would have to modify the code so that Sheet2 writes row by row starting with the last free row.

Answer 1

function button1() {
  const ss = SpreadsheetApp.getActive();
  const sht1 = ss.getSheetByName('Sheet0');
  const sht2 = ss.getSheetByName('Sheet1');
  const rng = sht1.getRange(2, 1, sht1.getLastRow() - 1, sht1.getLastColumn());
  const vs = rng.getValues();
  let o = vs.forEach((r, i) => {
    if (r[2] == true) {
      sht2.getRange(i + 2, 4, 1, 2).setValues([[r[0], r[1]]]);
    }
  })
}

Answer 2

Modification points:

• In your script, sht is not declared. So, an error occurs at var rng=sht.getRange(1,1,lastRow,6);. But you say But this code writes the data right next to the row.. So, I'm worried that your showing script might be different from your actual script.

• From sht2.getRange(i+1,4).setValue(rngA[i][0]); and sht2.getRange(i+1,5).setValue(rngA[i][1]);, you want to put the values from column "D".

• In your question, "Sheet1" and "Sheet2" are used. But, in your script and your image, "tab1" and "tab2" are used. In this answer, I use "tab1" and "tab2" . Please be careful about this.

About I would have to modify the code so that Sheet2 writes row by row starting with the last free row., from your question, I guessed the following 2 patterns.

Pattern 1:

In this pattern, the next row of the last row of "tab2" is the position you want to put.

function button1() {
  var ss = SpreadsheetApp.getActive();
  var sht1 = ss.getSheetByName('tab1');
  var sht2 = ss.getSheetByName('tab2');
  var lastRow = sht1.getLastRow();
  var rng = sht1.getRange(1, 1, lastRow, 6);
  var rngA = rng.getValues();

  // I modified the below script.
  var values = rngA.reduce((ar, r) => {
    if (r[2] === true) {
      ar.push(r.splice(0, 2));
    }
    return ar;
  }, []);
  sht2.getRange(sht2.getLastRow() + 1, 4, values.length, values[0].length).setValues(values);
}

• When this script is run, the filtered values are put from column "D" of the last row of "tab2".

Pattern 2:

In this pattern, the next row of the last row of "tab2" is not the position you want to put.

function button1() {
  var ss = SpreadsheetApp.getActive();
  var sht1 = ss.getSheetByName('tab1');
  var sht2 = ss.getSheetByName('tab2');
  var lastRow = sht1.getLastRow();
  var rng = sht1.getRange(1, 1, lastRow, 6);
  var rngA = rng.getValues();

  // I modified the below script.
  // https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };
  var values = rngA.reduce((ar, r) => {
    if (r[2] === true) {
      ar.push(r.splice(0, 2));
    }
    return ar;
  }, []);
  sht2.getRange(sht2.get1stNonEmptyRowFromBottom(4) + 1, 4, values.length, values[0].length).setValues(values);
}

• When this script is run, the filtered values are put from column "D" of the last row of column "D" of "tab2".

Note:

• In this modification, from sht2.getRange(i+1,4).setValue(rngA[i][0]); and sht2.getRange(i+1,5).setValue(rngA[i][1]);, you want to put the values from column "D". For example, if you want to put the values from column "A",
  • Please modify sht2.getRange(sht2.getLastRow() + 1, 4, values.length, values[0].length).setValues(values); to sht2.getRange(sht2.getLastRow() + 1, 1, values.length, values[0].length).setValues(values); for the pattern 1.
  • Please modify sht2.getRange(sht2.get1stNonEmptyRowFromBottom(4) + 1, 4, values.length, values[0].length).setValues(values); to sht2.getRange(sht2.get1stNonEmptyRowFromBottom(1) + 1, 1, values.length, values[0].length).setValues(values); for the pattern 2.

Added 1:

About your comment posted as an answer,

is it possible to search for the last free cell in the first column only and then accept this as the last line? I ask this because I have formulas in 2 columns and these are otherwise recognized as parts.

If my understanding of the above comment is correct, I have already mentioned the solution in my answer. But, from your comment, I'm worried that my English might not be correct. So, I would like to show the modified script for putting the values to the next row of the last row of column "A" of "tab2" as follows. By the way, I couldn't understand which script did you test from your question. So, I modified both patterns as follows.

Pattern 1:

function button1() {
  var ss = SpreadsheetApp.getActive();
  var sht1 = ss.getSheetByName('tab1');
  var sht2 = ss.getSheetByName('tab2');
  var lastRow = sht1.getLastRow();
  var rng = sht1.getRange(1, 1, lastRow, 6);
  var rngA = rng.getValues();

  // I modified the below script.
  var values = rngA.reduce((ar, r) => {
    if (r[2] === true) {
      ar.push(r.splice(0, 2));
    }
    return ar;
  }, []);
  sht2.getRange(sht2.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}

Pattern 2:

function button1() {
  var ss = SpreadsheetApp.getActive();
  var sht1 = ss.getSheetByName('tab1');
  var sht2 = ss.getSheetByName('tab2');
  var lastRow = sht1.getLastRow();
  var rng = sht1.getRange(1, 1, lastRow, 6);
  var rngA = rng.getValues();

  // I modified the below script.
  // https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };
  var values = rngA.reduce((ar, r) => {
    if (r[2] === true) {
      ar.push(r.splice(0, 2));
    }
    return ar;
  }, []);
  sht2.getRange(sht2.get1stNonEmptyRowFromBottom(1) + 1, 1, values.length, values[0].length).setValues(values);
}

Added 2:

From your following additional request,

Pattern2 works perfectly for me. Now I only need the following: How can I now delete the cells of columns A and B in sheet "tab1" after the copying process and at the same time make the checkboxes unchecked again? Can I add this to the existing code?

In this case, how about the following sample script?

Sample script:

function button1() {
  var ss = SpreadsheetApp.getActive();
  var sht1 = ss.getSheetByName('tab1');
  var sht2 = ss.getSheetByName('tab2');
  var lastRow = sht1.getLastRow();
  var rng = sht1.getRange(1, 1, lastRow, 6);
  var rngA = rng.getValues();

  // I modified the below script.
  // https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };
  var { values, ranges } = rngA.reduce((o, r, i) => {
    if (r[2] === true) {
      o.values.push(r.splice(0, 2));
      o.ranges.push(`A${i + 1}:B${i + 1}`);
    }
    return o;
  }, { values: [], ranges: [] });
  if (values.length == 0) return;
  sht2.getRange(sht2.get1stNonEmptyRowFromBottom(1) + 1, 1, values.length, values[0].length).setValues(values);
  sht1.getRangeList(ranges).clearContent(); // If you want to clear all rows of columns "A" and "B", please use sht1.getRange("A2:B" + sht1.getLastRow()).clearContent();
  sht1.getRange("C1:C" + sht1.getLastRow()).uncheck();
}

Answer 3

simple script I needed it myself recently

function copyData() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

  // Find the last row in Sheet2 and move to the next row
  var lastRow = sheet2.getLastRow();
  var newRow = lastRow + 1;

  // Check if the checkbox is selected in Sheet1
  var checkboxValue = sheet1.getRange("C1").getValue(); // Assuming the checkbox is in cell C1

  if (checkboxValue === true) {
    // Copy data from Sheet1 to Sheet2
    var data = sheet1.getRange("A:B").getValues(); // Assuming the data to be copied is in columns A and B
    sheet2.getRange(newRow, 1, data.length, data[0].length).setValues(data);

    // Clear the content of the cells in Sheet1 and reset the checkbox
    sheet1.getRange("A:B").clearContent(); // Assuming the data to be cleared is in columns A and B
    sheet1.getRange("C1").setValue(false); // Assuming the checkbox is in cell C1
  }
}