Google-sheets – If cell equals a value then move row to different Google Spreadsheet

google sheetsgoogle-apps-script

I've got a script that sorts a Google Spreadsheet when a value in a column is changed.

I'd like to add to the script the following functionality:

If the value for column E = "Complete" then the entire row is moved to
a different spreadsheet (that is a different tab in the same Google
Spreadsheet document).

The row would either be inserted, or appended to the bottom of the spreadsheet in the first blank row.

Is this possible?

Best Answer

Yes, I currently do this for a form system once it receives all necessary approvals I move all of the form submission's values to a new sheet that only contains approved submissions.

The following code gets the values of the row you would like to move and copies it (maintaining the original row) to the destination sheet. Assume that the submissionrow variable is the index of the row you want to move and the sheet variable is the source sheet you are checking the values on.

var lastcol = sheet.getLastColumn();
var rowvalues = sheet.getRange(submissionrow,1,1,lastcol).getValues();
var destinationsheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(nameofthedestinationsheet);
var rownum = destinationsheet.getLastRow() + 1;
destinationsheet.getRange(rownum, 1,1,lastcol).setValues(rowvalues);

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Best Answer

Related Solutions

Google Sheets – How to Refresh a Cell

Google-sheets – Fetch cell row and column indices in Google Sheets

Code

Explained

Screenshot

Example

Related Topic