Move Data from Form Response Sheet to Different Sheet using Button

Question

I have data in "Form Response Sheet". When user submit the form data saved in "Form Response Sheet". I want to move data from "Form Response Sheet" to "verified Form Response" sheet when user mark true in column w and Press "Submit" button. The row should be deleted from the "Form Response sheet" and Should be moved to Verified Form Response sheet.

The Verified Form Response Sheet contains daily data which was moved from Form Response Sheet. The moved data should be moved after the data earlier moved. The Sheet is also shared with my team member. The Script I used is not running with him.

The link of the sheet is as below:

https://docs.google.com/spreadsheets/d/1LUUEZ7sSjBy-WWL3TZt8l6sqtrEBn3dZ03iRDTY1bF0/edit?usp=sharing

The script I am using is as below:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Form responses 1");
  var pasteSheet = ss.getSheetByName("Verified Form Responses");
  
  var pasteSheet_size=pasteSheet.getRange('A2:A').getValues().filter(String).length;
  var source_size=copySheet.getRange('A2:A').getValues().filter(String).length;
  var source_range = copySheet.getRange(2,1,source_size,copySheet.getLastColumn());

  var move_data = source_range.getValues().filter(row=>row[22]==true);
  var source_data = source_range.getValues().filter(row=>row[22]==false);
  pasteSheet.getRange(pasteSheet_size+1,1,move_data.length,move_data[0].length).setValues(move_data);
  
  source_range.clearContent();
  copySheet.getRange(2,1,source_data.length,source_data[0].length).setValues(source_data);
        
}

Answer 1

Issue:

• Your goal is to move data between a form responses sheet to another sheet. The issue is that you need to remove the row after you transfer the entries from the form responses sheet, otherwise you will end up with a response sheet full o blank rows on the top (as it is the case right now).
• Also the current solution you use depends on the size of column A in Form responses 1 sheet. Meaning that if you have blank rows in that sheet, you will get back the wrong number of rows.

---

Explanation / Solution:

The first step towards the solution is to find the indexes that column W (checkboxes column) contains true. To achieve that, you can use reduce():

  const w_vals = src_sh.getRange('W2:W'+src_sh.getLastRow()).getValues().flat();
  const inds = w_vals.reduce(
  (out, bool, index) => bool ? out.concat(index) : out, 
  []
  )

inds contains the indexes of the rows you want to move to the other sheet. Since we start counting from W2, the desired row will be given by inds[i]+2.

---

The second step is to iterate over inds. In each iteration, we store the data that we want to move into the data array and then we delete that row:

var temp = src_sh.getRange(inds[i]+2,1,1,src_sh.getLastColumn()).getValues().flat();
data.push(temp)
src_sh.deleteRow(inds[i]+2);

Keep in mind that the for loop iterates backwards, because every time we delete a row we change the structure of the sheet and inds wouldn't correspond to the correct row.

---

Finally, we efficiently (using setValues) copy the data to destination sheet:

dst_sh.getRange(dst_sh_size+1,1,data.length,data[0].length).setValues(data);

---

Snippet:

Before you use this solution, delete all the top empty rows in the Form responses 1 sheet (rows 2-108).

function CopyInfo() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const src_sh = ss.getSheetByName("Form responses 1");
  const dst_sh = ss.getSheetByName("Verified Form Responses");
  const dst_sh_size=dst_sh.getRange('A2:A').getValues().filter(String).length;
  
  const w_vals = src_sh.getRange('W2:W'+src_sh.getLastRow()).getValues().flat();
  const inds = w_vals.reduce(
  (out, bool, index) => bool ? out.concat(index) : out, 
  []
  )
   
  const data = [];  
  if ( inds.length != 0) {
  for (let i = inds.length -1 ; i>=0; i--){
  var temp = src_sh.getRange(inds[i]+2,1,1,src_sh.getLastColumn()).getValues().flat();
  data.push(temp)
  src_sh.deleteRow(inds[i]+2);
  }
    
   dst_sh.getRange(dst_sh_size+1,1,data.length,data[0].length).setValues(data);     
  }
}