Google Apps Script: Search for fields in 1 sheet, copy to location in different sheet

Question

I'm new to google apps scripting, and when I search for some things I get different answers than I'm looking for (for example if I google: app script search sheet, I get instructions for adding a search box to a spreadsheet).

I'm trying to make a script that will copy grades from an automatically generated spreadsheet that contains student quiz grades (generated by a google form quiz), to a master grading sheet (these are 2 different documents). I'm a bit confused because it seems that most of the examples I see about copying information don't copy between two different doc's, but rather from two different sheets (tabs) within one doc. Each student has a login name, for example john smith might be jsmith01. Each row in a spreadsheet corresponds to 1 student. There are a bunch of columns, but I'm trying to work with the "login" column and the "quiz" column. The login column holds the students login (ex jsmith01) and the quiz column contains the quiz grade. Here's some pseudocode for what I'm trying to do:

quizGrades = spreadsheet with results automatically created by quiz form
masterGrades = spreadsheet that I need to copy quiz grades into

for curStudent in rows on quizGrades sheet{
    studentLogin = login name of curStudent in login column in quizGrades sheet
    studentGrade = value (quiz grade) for curStudent in quiz column in quizGrades sheet
    currStudentRow = row where login col. value in masterGrades sheet matches studentLogin

    copy studentGrade into grade col. in currentStudentRow in masterGrades sheet
}

Answer 1

You need to open one of the spreadsheet by ID

function moveAnswersToOtherSpreadsheet() {
  var answers,masterGradesSS,quizGradesSS,sheetTabWithAnswers;//Declare variables without assigning a value

  masterGradesSS = SpreadsheetApp.getActiveSpreadsheet();//spreadsheet with results automatically created by quiz form
  quizGradesSS = SpreadsheetApp.openById("Put the ID here"); //spreadsheet to copy quiz grades into

  sheetTabWithAnswers = masterGradesSS.getSheetByName("name here");

  //getRange(start row, start column, number of Rows to get, number of Columns to get)
  answers = sheetTabWithAnswers.getRange(1, 1, sheetTabWithAnswers.getLastRow(), sheetTabWithAnswers.getLastColumn()).getValues();
}

Answer 2

When I started out I used to hold the source data in arrays and then iterate the data out into the target sheet.

Then I found the copy method in the API documentation. Hope this is helpful:

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var source = ss.getSheets()[0];

 var destination = SpreadsheetApp.openById("abc1234567");
 var destinationSheet = destination.getSheets()[1];

 var range = source.getRange("B2:D4");

 // This copies the data in B2:D4 in the source sheet to
 // D4:F6 in the second spreadSheet
 range.copyValuesToRange(destinationSheet, 4, 6, 4, 6);

Answer 3

Perhaps this is helpful. I made three functions (which I have used before). They return row and column coordinates of a searched for term in a spreadsheet.

function searchForStudentId(SPREADSHEET_ID, SHEET_NAME, studentId) {
    var locatedCells = [];
    var ss = SpreadsheetApp.openById(SPREADSHEET_ID);
    var searchLocation = ss.getSheetByName(SHEET_NAME).getDataRange().getValues();        
    //Loops to find the search term. 
        for (var j = 0, jLen = searchLocation.length; j < jLen; j++) {
            for (var k = 0, kLen = searchLocation.length; k < kLen; k++) {
                var find = studentId;
                if (find == searchLocation[j][k]) {
                    locatedCells.push({ 'found': (j + 1) + "," + (k + 1)});
                }
            }
        }
     //   Logger.log(locatedCells);
        return(locatedCells)
    }


 function LocateStudentIdsInSource (){
  var SPREADSHEET_ID = "ADD ID"
  var SHEET_NAME = "Sheet1"
  var studentId = "1234567"
  var studentIdSourceLocation = searchForStudentId(SPREADSHEET_ID,    SHEET_NAME, studentId)
  Logger.log(studentIdSourceLocation);
}


  function LocateStudentIdsInTarget (){
  var SPREADSHEET_ID = "ADD ID"
  var SHEET_NAME = "Sheet1"
  var studentId = "1234567"
  var studentIdTargetLocation = searchForStudentId(SPREADSHEET_ID, SHEET_NAME, studentId)
  Logger.log(studentIdTargetLocation)
  }