CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
gonzalo2000
gonzalo2000

Reputation: 648

Map data from one sheet to another

I have a situation similar to the one in this question, where I'm trying to map user demographic data to a responses sheet upon form submission. The solution has been working correctly in all situations. Whenever one of the user IDs doesn't correspond to an ID available on the roster, it simply skips that row and keeps going to the next row.

I' running into issues when I tried to modify the code to include one more column (I want to scan and map column I from the "roster sheet" into the "responses sheet" as well). This is the original solution:

function extractId() {
  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);
}

And this is how I modified it:

function extractId() {
  var responsesSheet = SpreadsheetApp.openById('1bDtDMkWDosKsf0EDV08VY-pzE8Okr5kqQU0vnNps6mo').getSheetByName('Form Responses 1');
  var rosterSheet = SpreadsheetApp.openById('196JpI3cvJZOynhqL0kyBoxqXap9NtykBG5RvCPtl7dA').getSheetByName('Sheet1');
  var valuesOfresponsesSheet = responsesSheet.getRange(2, 2, responsesSheet.getLastRow() - 1, 1).getValues();
  var valuesOfrosterSheet = rosterSheet.getRange(2, 1, rosterSheet.getLastRow() - 1, 9).getValues();
  var obj = valuesOfrosterSheet.reduce(function(o, e) {
    o[e[0]] = [e[0], e[1], e[5], e[6], e[7], e[8]];
    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);
}

The issue I'm running into is that whenever there isn't an ID match (i.e. if Column B of "responses sheet" doesn't match one of the items of column A in "roster sheet") I get the following error: The number of columns in the data does not match the number of columns in the range. The data has 5 but the range has 6. (line 14, file "Code"). The code stops working at whichever row the non-matching ID happens to be.

What I would like for is that if there is no ID match for the code to simply keep going to the next row.

Upvotes: 1

Views: 84

Answers (1)

Tanaike
Tanaike

Reputation: 201533

How about this modification?

In your modification, I could confirm that one element was added like o[e[0]] = [e[0], e[1], e[5], e[6], e[7], e[8]]. In this case, e[8] was added. So in this case, it is required to also add one element to ["","","","",""] like ["","","","","",""]. I think that this is the reason of your issue.

So please modify your bottom script in your question as follows.

From:

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

To:

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

If this was not the direct solution, I apologize. At that time, can you provide the sample Spreadsheet for replicating your issue. By this, I would like to confirm it.

Upvotes: 1

Related Questions