Google Apps脚本:在电子表格之间移动数据 [英] Google Apps Script: Move data between spreadsheets

问题描述

我正在尝试使用Google Apps脚本在两个Google电子表格之间移动数据.

I'm trying to use Google Apps Script to move data between two Google spreadsheets.

名为Ex_ID的电子表格为每位员工提供一张工作表和一行，每行具有六个重要值，包括ID号和五个数据值.

The spreadsheet named Ex_ID has one sheet and one row for each employee, each row has six important values including an id number and five data values.

名为CR_ID的电子表格为每位员工提供了一张不同的工作表，每张工作表都有一个ID号和五个要输入数据的单元格.

The spreadsheet named CR_ID has a different sheet for each employee and each sheet has an id number and five cells where I need to enter the data.

我正在尝试编写一个脚本，该脚本将数据值从Ex_ID移到ID值匹配的CR_ID. (注意:由于我仍在尝试查看代码的工作方式，因此我将示例限制为五个数据值之一，而仅在三名员工上进行了测试.)

I'm trying to write a script that moves data values from Ex_ID to CR_ID where the id values match. (Note: Since I'm still trying to see how the code can work, I limited the example to one of the five data values and I'm only testing it on three employees.)

function myFunction() {
  var CR_ID = "1BbAmoSsS_2-nvOxv_l5gZ8ftXclD5muWs0ZAmkDUR-Y";
  var ssCR = SpreadsheetApp.openById(CR_ID);
  var sCR = ssCR.getSheets();
  var Ex_ID = "1lkpXdWdbfe8Wkj1RGQOb-pWjEQe41hstKj2i4TxMQd8";
  var ssEx = SpreadsheetApp.openById(Ex_ID);
  var sEx = ssEx.getSheetByName('Sheet1');
  for (var i = 2; i = 4; i++) {
    //Get id value from Ex_ID 
    var ExCell = sEx.getRange(i, 2).getValue();
    for (var j = 0; j = 2; j++) {
      var sheet = sCR[j];
      //Get id value from CR_ID
      var val = sheet.getRange(1, 7).getValue();
      if (ExCell[i] == val[j]) {
        var ExWrite = sEx.getRange(i, 11).getValue();
        sheet.getRange(3, 20).setValue(ExWrite);
      }    
    }  
  }
}

最好

Mayne Dela

Mayne Dela

推荐答案

我不知道您的数据是如何组织的以及您希望将它们放在表中的什么位置，但是我会尝试这样的事情:

I don't know how your data is organized and where you want to put them on the sheets but I would try something like this:

function myFunction() 
{
  var ess = SpreadsheetApp.openById("1BbAmoSsS_2-nvOxv_l5gZ8ftXclD5muWs0ZAmkDUR-Y");
  var es = ess.getSheets();

  var dss = SpreadsheetApp.openById("1lkpXdWdbfe8Wkj1RGQOb-pWjEQe41hstKj2i4TxMQd8");
  var ds = dss.getSheetByName('Sheet1');
  var dr = ds.getDataRange();
  var dA = dr.getValues();
  for(var i=1;i<dA.length;i++)//started with 1 assuming that you have a header.  
  {
      var row=dA[i];//loop through rows of employee data I will assume data to be in this  ID,DataValue1,DataValue2,DataValue3,DataValue4,DataValue5
      for(var j=0;j<es.length;j++)
      {
        if(row[0]==es[j].getSheetName())//checking the ID value assuming it is row[0];
        {
          var sht=es[j];
          sht.getRange('D1').setValue(row[1]);//DataValue1
          sht.getRange('D2').setValue(row[2]);//DataValue2
          etc...
        }

      }
  }

}

这篇关于Google Apps脚本:在电子表格之间移动数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！