使用应用脚本将数据整合到一个Google主表中 [英] Consolidate data into one master Google sheet using App Scripts

问题描述

我正在尝试将多个选项卡中的数据合并到一个合并的工作表中.每个选项卡都是单独的表单，并且具有相同的格式.在合并的工作表上，我想重新排列数据，以便数据字段名称在列中，而数据值在行中.我尝试了以下方法:

I am trying to consolidate data from multiple tabs into a consolidated sheet. Each tab is an individual form and has the same format. On the consolidated sheet, I want to re-arrange the data so the data field name is in a column, and data values are in rows. I tried the following:

function consolidateData(){

// defined all variables
   var sheetNames = [];
   var dataSheet = [];
   var dataValues = [];
   var conso=[];
   var header = [["Faculty Name","Faculty ID","Date of Joining"]];
   var ws = SpreadsheetApp.getActiveSpreadsheet();

 // get all sheets
   var allsheets = ws.getSheets();

 for(var s in allsheets)
 var sheet = allsheets[s];    
 sheetNames[s] = sheet.getName();
 dataSheet[s] = ws.getSheetByName(sheetNames[s]);

// writing data into new sheet
  var newSheet = ws.insertSheet().setName("Consolidated_Data");
   newSheet.getRange("A1:C1").setValues(header);

  var name = dataSheet[s].getRange("B1").getValue();
  var id = dataSheet[s].getRange("B3").getValue();
  var doj = dataSheet[s].getRange("B5").getValue();

 var faculty = [(name),(id),(doj)];//convert into array
 var facultycount = faculty.length;
  for (var i = 0; i < faculty.length; i++)

  //Loop through all rows and write them out starting on ROW2

 {
  newSheet.getRange(2 + i, 1).setValue(faculty[0]);//
  newSheet.getRange(2 + i, 2).setValue(faculty[1]);//
  newSheet.getRange(2 + i, 3).setValue(faculty[2]);// 

  }
  } 

有四个选项卡，我希望看到Consolidated_Data选项卡中每个选项卡的结果.但是我只看到最后一个标签数据被重复插入.有人可以帮忙吗?谢谢你.合并数据表单个标签的示例

There are four tabs and I expect to see results from each tab in the Consolidated_Data tab. But I only saw the last tab data got inserted repeatedly. Can anyone help? Thank you. Consolidated Data Sheet Example of an individual tab

推荐答案

遍历所有工作表时，在for循环后没有使用花括号-

While traversing through all your sheets, you haven't used curly braces after the for loop -

for(var s in allsheets)

因此它正在运行循环，并且s的值保持在allsheets的最后一个索引上.

So it's running the loop and the value of s stays at the last index of allsheets.

但是，我是否可以建议我测试过的简化版本-

However, might I suggest a simplified version I have tested out -

function consolidateData () {

  const headers = ["Faculty Name", "Faculty ID", "Date of joining"];
  const rows = { "name": 0, "id": 2, "doj": 4 };
  const consolidatedSheetName = "Consolidated_Data";

  const ss = SpreadsheetApp.getActive();
  const sheets = ss.getSheets();
  let consolidatedValues = [];  

  // Setting headers
  consolidatedValues.push(headers); 

  // Fetching values
  for(let sheet of sheets) {

    if(sheet.getName()==consolidatedSheetName) { continue; }
    let data = sheet.getRange("B1:B5").getValues();
    let faculty = [ data[rows.name][0], data[rows.id][0], data[rows.doj][0] ];
    consolidatedValues.push(faculty);
  }

  // Adding to sheet
  let consolidatedSheet = ss.getSheetByName(consolidatedSheetName);
  if(!consolidatedSheet) {
    consolidatedSheet = ss.insertSheet().setName(consolidatedSheetName);
  }

  let range = consolidatedSheet.getRange(1, 1, consolidatedValues.length, consolidatedValues[0].length); // 1, 1, 3, 3
  range.setValues(consolidatedValues);
}

这篇关于使用应用脚本将数据整合到一个Google主表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！