当前位置: 动力学知识库 > 问答 > 编程问答 >

javascript - Data copying with Google Apps Script

问题描述:

I am trying to write a script in Google Apps Script that takes cell information from one sheet and copies it to another sheet, both for just grabbing certain columns to display on the second sheet and also a condition based on the values inside cells in a certain column. Here is what I have so far:

function onMyEdit() {

var myMaster = SpreadsheetApp.openById("xxxxx");

var masterSheet = myMaster.setActiveSheet(myMaster.getSheets()[0]);

var myNames = SpreadsheetApp.openById("xxxxx");

var namesSheet = myNames.setActiveSheet(myNames.getSheets()[0]);

var row1 = masterSheet.getRange(1, 1, masterSheet.getLastRow(), 1);

var rowV = row1.getValues();

var firstArray = masterSheet.getDataRange().getValues();

var dataList = [];

for (var i = 1; i < rowV.length; i++) {

dataList.push(firstArray[i][0]);

dataList.push(firstArray[i][1]);

dataList.push(firstArray[i][2]);

dataList.push(firstArray[i][3]);

}

for (var j = 0; j < rowV.length - 1; j++) {

namesSheet.getRange(2, j + 1, 1, 1).setValue(dataList[j]);

}

}

So as of now it only works on one row, starting from the second row (to allow for column headers). And I suppose when I want to grab rows conditionally based on cell data, I will use an 'if' statement for the condition inside the 'for' loop, but I want the data to copy to the next available row in both sheets. I suppose I'd use something like:

' getLastRow + 1 '

or something like that. I need this code to be as efficient as possible because of the amount of data and its purpose. I am pretty new to programming so please explain in detail, and thanks again.

网友答案:

I'm not sure I understood exactly what you wanted to do but -from what I understood- this code snippet should give you a better way to start with...

(I added a few comments to explain in the code itself)

function onMyEdit() {
  var myMaster    = SpreadsheetApp.openById("MasterSheet ID");
  var masterSheet = myMaster.getSheets()[0]; // get 1rst sheet
  var myNames     = SpreadsheetApp.openById("NamesSheet ID");
  var namesSheet  = myNames.getSheets()[0]; // get 1rst sheet
  var firstArray  = masterSheet.getDataRange().getValues();
  var dataList    = [];
  for ( r = 1; r < firstArray.length; r++) { // iterate the first col of masterSheet
      if(firstArray[r][0]=='some condition'){ // if value in the first column == 'some condition get the second column cell in the new array (here you could change what you want to get)
        dataList.push([firstArray[r][1]])
  }
  }
  Logger.log(dataList)
  if(dataList.length>0){
namesSheet.getRange(1,namesSheet.getLastColumn()+1,dataList.length,1).setValues(dataList);//copy data in a column after last col
}
}
分享给朋友:
您可能感兴趣的文章:
随机阅读: