时间戳

首页 > 解决方案 > 为什么要复制所有值？

google-apps-script - 为什么要复制所有值？

问题描述

不确定为什么它要复制所有值，而不仅仅是月份不是一个的值。试图只复制月数大于一的行。

以及为什么在复制的时候，会漏掉客户 Dave 的第一行？

电子表格值

function CopyUsersToNextMonth() {
  //define spreadsheet
  var spreadsheet = SpreadsheetApp.getActive();
  var month = 0;
  
  for (var counter = 3; counter <= 8; counter = counter + 1) {
    spreadsheet.setActiveRange(spreadsheet.getRange('E' + counter));
    month = spreadsheet.getActiveRange().getValue();
    Logger.log(spreadsheet.getActiveRange().getValue());
    
    //if the month is not 1
    if(month != 1){
      //get range of values
      spreadsheet.getRange('B' + counter +':' + 'E' + counter).activate();
      spreadsheet.setCurrentCell(spreadsheet.getRange('E' + counter));
      //call to second sheet
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('B'), true);
      //choose cell to paste line from
      spreadsheet.getRange('B' + counter).activate();
      
      //select the current line
      var currentRange = spreadsheet.getRange('A!B' + counter + ':E' + counter);
      
      currentRange.copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
      
    }  
    
  }
  
};

编辑 1：添加了另一张图片以显示所需的结果；编辑 2：添加到假人的链接

谷歌表格 页面 B

标签： google-apps-scriptgoogle-sheets

解决方案

这对我有用：

function CopyUsersToNextMonth() {
  //define spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetA = spreadsheet.getSheetByName("A");
  var spreadsheetB = spreadsheet.getSheetByName("B");
  for (var counter = 3; counter <= 8; counter++) {
    var month = spreadsheetA.getRange('E' + String(counter) + ":E" + String(counter)).getValue();
    
    //if the month is not 1
    Logger.log(month);
    if(Number(month) > 1){
      //get range of values
      var destinationRange = spreadsheetB.getRange('B' + counter +':' + 'E' + counter);
      Logger.log(JSON.stringify(myRange.getValues()));
      
      //call to second sheet
      
      //choose cell to paste line from
      spreadsheetB.getRange('B' + counter).activate();
      
      //select the current line
      var fromRange = spreadsheetA.getRange('A!B' + counter + ':E' + counter);
      Logger.log(JSON.stringify(currentRange.getValues()));
      fromRange.copyTo(destinationRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); 
    }
  }
}

我必须分别区分来自两个不同工作表（名为 A 和 B）的范围，因为依赖活动范围和突出显示某些单元格一直很棘手，至少对我而言，如果用户同时在工作表上然后它使任务变得更加困难。

Rendition #2： 对于非常大的数据集，您不想逐行循环并且对电子表格进行如此多的调用，而是希望分批工作，所以这是我对该问题的第二次演绎：

function CopyUsersToNextMonthBatch() {
  //define spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetA = spreadsheet.getSheetByName("A");
  var spreadsheetB = spreadsheet.getSheetByName("B");
  var dataRangeOnSpreadsheetA = spreadsheetA.getDataRange();
  //Get all data from 2nd row 2nd column to bottom row and 4 columns to the right into a 2d array
  var entireTableSpreadsheetA = spreadsheetA.getRange(2, 2, dataRangeOnSpreadsheetA.getLastRow() - 1, 4).getValues();
  Logger.log(JSON.stringify(entireTableSpreadsheetA));
  //Filter 2d array and remove all inner Arrays where (month <= 1)
  var entireTableToCopyOverToSpreadsheetB = entireTableSpreadsheetA.filter((nestedArray) => {
      var amountOfMonths = nestedArray[nestedArray.length - 1]; //Gets last element in inner array
      //Either keep the row if month > 1 OR keep the row if it is the title row
      return amountOfMonths > 1 || amountOfMonths == "Amount of months";
  });
  Logger.log(JSON.stringify(entireTableToCopyOverToSpreadsheetB));
  var dataRangeOnSpreadsheetBWhereNewTableWillBeAdded = spreadsheetB.getRange(2, 2, entireTableToCopyOverToSpreadsheetB.length, 4);
  dataRangeOnSpreadsheetBWhereNewTableWillBeAdded.setValues(entireTableToCopyOverToSpreadsheetB);
  
}

不仅解决方案更清洁，而且结果也更清洁！

推荐阅读