将数据从一张纸映射到另一张纸 [英] 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从花名册"扫描并映射到响应表").这是原始的解决方案:

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);
}

这就是我的修改方式:

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);
}

我遇到的问题是，只要没有ID匹配(即，如果响应表"的B列与名册"中的A列之一都不匹配)，我就会得到出现以下错误: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").该代码将在不匹配ID发生的任何行停止工作.

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.

我想要的是，如果没有与ID匹配的代码，只需继续进入下一行即可.

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

推荐答案

此修改如何?

在您的修改中，我可以确认添加了一个元素，例如o[e[0]] = [e[0], e[1], e[5], e[6], e[7], e[8]].在这种情况下，添加了e[8].因此，在这种情况下，还需要像["","","","","",""]一样向["","","","",""]添加一个元素.我认为这是您遇到问题的原因.

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.

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

收件人:

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.

这篇关于将数据从一张纸映射到另一张纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！