Use For Loop To Map Data From One Google Sheet To Another One

I'm still fairly new with Google Scripts. I have a student roster Google Sheet with demographic information, including ID numbers. I also have a responses Sheet that automatically

Solution 1:

• You want to achieve "Sample end-result responses sheet" from the values of "roster sheet" in the images in your question.
  • In your situation, "responses sheet" has only the column "B" which has the email addresses.
  • You want to put the values that the ID from the email address is the same with the column "A" in "roster sheet".
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

The flow of this modified script is as follows.

1. Retrieve the values of the column "B" from "responses sheet".
2. Retrieve the values from "roster sheet".
3. Create an object for searching IDs.
4. Create the result values as an array.
5. Put the values to "responses sheet".

Modified script:

functionextractId() {
  var responsesSheet = SpreadsheetApp.openById('XXXXXXX').getSheetByName('Sheet1');
  var rosterSheet = SpreadsheetApp.openById('XXXXXXX').getSheetByName('Sheet1');
  var valuesOfresponsesSheet = responsesSheet.getRange(2, 2, responsesSheet.getLastRow() - 1, 1).getValues();
  var valuesOfrosterSheet = rosterSheet.getRange(2, 1, rosterSheet.getLastRow() - 1, 8).getValues();
  var obj = valuesOfrosterSheet.reduce(function(o, e) {
    o[e[0]] = [e[0], e[1], e[5], e[6], e[7]];
    return o;
  }, {});

  // var resultValues = valuesOfresponsesSheet.map(function(e) {return obj[e[0].replace(/\@(.*)/i,"")]});var resultValues = valuesOfresponsesSheet.map(function(e) {return obj[e[0].toString().replace(/\@(.*)/i,"")] || ["","","","",""]}); // Modified

  responsesSheet.getRange(2, 3, resultValues.length, resultValues[0].length).setValues(resultValues);
}

Reference:

• reduce()