google-apps-script - 使用应用程序脚本在谷歌表格中透视或转置数据

Question

我希望有人可以帮助我使用应用程序脚本以编程方式在谷歌工作表中旋转或转置数据。

以下是我到目前为止所做的。我很确定这远非实现这一目标的正确/最佳方式，因此我在这里伸出援手。数据用于包含 3 个问题的调查。该脚本运行良好，但没有考虑任何调查，其中没有为某个问题或 3 个问题中的任何一个选择答案。我很确定这是因为每个语句都需要一个else语句部分，If但无法弄清楚如何在每个循环中编写该逻辑。

我还尝试通过将其转换为对象数组来进行试验，在其中我尝试使用 ID 键来匹配日期、来源和三个问题，但也无法正常工作。

我附上了源数据的样子和我想要实现的目标的图像，以及在我执行当前拥有的脚本后它当前的样子。

我写的代码是在我上传的 3 张图片之后。

希望我已经正确解释了这一切。我将不胜感激这方面的任何帮助。

源数据如下所示：

这就是我想要实现的目标：

这是上面代码运行后数据的样子：

function sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('sampleData');
  var range = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());
  var values = range.getValues();
  
  var resultsSh = ss.getSheetByName('sampleResults');
  
  //console.log(values);

  var source = values.filter(function(row){
    if(row[2] === 'Source'){
      return true;
      } else {
        return false;
        }    
    });

    //console.log(source);

    var q1 = values.filter(function(row){
    if(row[2] === 'Question1'){
      return true;
      } else {
        return false;
        }    
    });
 
    //console.log(q1); 
    
    var q2 = values.filter(function(row){
    if(row[2] === 'Question2'){
      return true;
      } else {
        return false;
        }    
    });
 
    //console.log(q2); 
 
    var q3 = values.filter(function(row){
    if(row[2] === 'Question3'){
      return true;
      } else {
        return false;
        }    
    });
    
    //console.log(q3);
    
    var result1 = [];
    
    for (i=0;i<source.length;i++){
      for (j=0;j<q1.length;j++){
         if(source[i][1] === q1[j][1]){
            result1.push([...source[i], ...q1[j]]);
        }
      }
    }
       
      //console.log(result1);
      
      var result2 = [];
      
      for (i=0;i<result1.length;i++){
        for (j=0;j<q2.length;j++){
            if (result1[i][1] === q2[j][1]) {
              result2.push([...result1[i],...q2[j]])
            }
          }
        }

        //console.log(result2);
        
        var final = [];
        
         for (i=0;i<result2.length;i++) {
           for (j=0;j<q3.length;j++) {
             if (result2[i][1] === q3[j][1]) {
               final.push([...result2[i], ...q3[j]])
               } 
             }
           }
         
         //console.log(final);
         
         var data = final.map(function(row){
           return [row[0].toLocaleString('en-GB').replace(/[',']/g,''), row[1], row[3], row[7], row[11], row[15], row[7] + row[11] + row[15]];
           });
           
         console.log(data);
        
     ss.getSheetByName('Sheet16').getRange(2, 1, data.length, data[0].length).setValues(data);
          
}

标签： google-apps-script

Answer 1

这可以通过一个for循环来完成，该循环检查每一行和 aswitch() case:以填充相应的数组元素并计算总和。

function sample() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('sampleData');
  var range = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());
  var values = range.getValues();
  var resultsSh = ss.getSheetByName('sampleResults');
  var lastRow = sheet.getLastRow();
  var lastID = values[0][1];
  var result = [];
  var sum = 0;

  // check each row
  for (i=0; i<lastRow-1; i++) {
    var currID = values[i][1];
    if ((currID != lastID) & sum) {
      result[result.length-1] = sum;
    }
      switch (values[i][2]) {
        case 'Source':
          result.push(values[i][0],values[i][1],values[i][3],'','','','');
          sum = 0;
        break;
        case 'Question1':
          result[result.length-4] = values[i][3];
          sum += values[i][3];
        break;
        case 'Question2':
          result[result.length-3] = values[i][3];
          sum += values[i][3];
        break;
        case 'Question3':
          result[result.length-2] = values[i][3];
          sum += values[i][3];
        break;
      }
    lastID = currID;
  }
  result[result.length-1] = sum;

  // convert to 2d array
  const result2d = [];
  while(result.length) result2d.push(result.splice(0,7));
  // put to results sheet
  var resultRange = resultsSh.getRange(2,1,result2d.length,result2d[0].length);
  resultRange.setValues(result2d);
}

样本数据：

样本结果：