Move data using Button But only True Entries in Google Form Response

Question

I Have data in "Form responses 1". I want to move data from "Form responses 1" sheet to "Verified Form Response" Sheet. The condition is that entries which are marked as True in Column W should move, not all the entries.

The link of the sheet is:

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

The code I am using is:

function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Form responses 1");
  var pasteSheet = ss.getSheetByName("Sheet5SS");
  
  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(1,1,source_size,copySheet.getLastColumn());

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

Hi, would you let me know if my answer solved your issue so I can modify it if it didn't ? Thanks — soMario

soMario soMario · Accepted Answer · 2020-10-07T09:31:48

Explanation:

You are very close.

You need to start the range from the second row:

copySheet.getRange(2,1,source_size,copySheet.getLastColumn());
and the checkboxes are in column W, therefore in column number 22:

filter(row=>row[22]==true);

Solution:

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);
        
}

Move data using Button But only True Entries in Google Form Response

1 Answers