Checkbox in Sheets as Enter button to Copy Data-CodePudding

I'm trying to figure out an onEdit function for my Google Sheet where, when a checkbox in J16 of the DATA PULL sheet is checked, it will copy the values of the cells in columns F15 to F20 and I17 to E20, then paste them into a new row on the REPORTS sheets on the respective columns and once unchecked it will move to another row, and when checked it will copy the new data.

Really need some help.

Here is the file (

Demo:

CodePudding user response：

Description

Your data set is rather complex so I didn't produce a data set to test this but I'm pretty sure it will work for you.

1st. Test that we are on the checkbox in J16 and that the checkbox is true.

2nd. Get the values from I17:I20 a 2D array in the form [[1],[2],[3]...]

3rd. Get the value from E15:E20 a 2D array in the form [[4],[5],[6]...]

4th. Concatinate the 2 arrays [[1],[2],[3]...[4],[5],[6]...]

This would produce a column on a spreadsheet. However we can use the Array.flat() to make it a row [1,2,3...4,5,6...].

Lastly we simply copy the row to the other sheet but we need to convert to a 2D array to use setValues() by enclosing in array brackets [values].

Code.gs

function onEdit(e) {
  let src = e.range.getSheet();
  let dest = e.source.getSheetByName("Reports"); 
  if( src.getName() === "Data Pull" ) {
    if( ( e.range.getA1Notation() === "J16" ) {
      if( e.value ) {
        let values = src.getRange(17,9,4,1).getValues();  // I17:I20
        values = values.concat(src.getRange(15,6,6,1).getValues());  // add E15:E20
        dest.getRange(dest.getLastRow() 1,5,1,values.length).setValues([values.flat()]);
      }
    }
  }
}

Reference