After someone submits a Google Form response, their responses go to the DropRequests sheet (although the form is not currently linked for reasons).

What we want to do is after someone submits their form, and their responses go to the DropRequests sheet, that if the value in column C matches that of column F in the StudentMatches sheet, it is moved to the OldMatches sheet. We have started on the code below but it does not yet work. Any ideas on how to make this functional and fix the issue with the range in the last line?

function moveMatch(){
var oldmatches = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("OldMatches");
var droprequest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DropRequests");
var currentmatches = SpreadsheetApp.openById('1dd9UhD2LpshCFVYizpf3OwI1XzPrq3AfqhMAO1iJ6Ns')

  value1 =  currentmatches.getRange("F:F").getDisplayValues();
  value2 = droprequest.getRange("C:C").getDisplayValues();

for(var i in value1)
  if(value2[0,i]=value1){
  
  currentmatches.getDataRange.getRow(0,i).moveTo(oldmatches.getLastRow())
}
}

Thank you again.

CodePudding user response：

I believe your goal is as follows.

• There are 3 sheets DropRequests, StudentMatches, OldMatches in your Spreadsheet.
• You want to retrieve the values of column "C" from DropRequests sheet, and want to compare these values with the values of column "F" of StudentMatches sheets.
• When the values are matched, you want to move the row from the StudentMatches sheet to the 1st empty row of OldMatches sheet.
• When moveTo is used, the moved row becomes the empty row. In your goal, you want this situation.

In this case, how about the following sample script?

Sample script:

function moveMatch() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet1 = ss.getSheetByName("DropRequests");
  var srcSheet2 = ss.getSheetByName("StudentMatches");
  var dstSheet = ss.getSheetByName("OldMatches");

  var srcValues1 = srcSheet1.getRange("C2:C"   srcSheet1.getLastRow()).getValues().map(([c]) => c);
  var [,...srcValues2] = srcSheet2.getDataRange().getValues();
  var values = srcValues2.flatMap((r, i) => srcValues1.includes(r[5]) ? i   2 : []);
  if (values.length == 0) return;
  var lastCol = srcSheet2.getLastColumn();
  var lastRow = dstSheet.getLastRow();
  values.forEach((r, i) => srcSheet2.getRange(r, 1, 1, lastCol).moveTo(dstSheet.getRange(lastRow   1   i, 1)));
}

• In this modification, the values are retrieved from the column "C" of "DropRequests" and "StudentMatches". And, the values of the column "C" of "DropRequests" are compared with the column "F" of "StudentMatches" sheet. When the values are matched, the row is moved from "StudentMatches" to "OldMatches".

Note:

• First, please check the sheet names, again.

• This sample script is for your question. So, when the Spreadsheet is changed, this script might not be able to be used. Please be careful this.

References:

• forEach()
• moveTo(target)