时间戳

首页 > 解决方案 > 每当插入新行时，创建一个新的电子表格并复制特定的单元格

google-apps-script - 每当插入新行时，创建一个新的电子表格并复制特定的单元格

问题描述

我有：

我想：

我最初使用 getActiveSpreadsheet 来获取 sourceSpreadsheet 变量，并认为在我创建新电子表格后，活动电子表格可能正在发生变化。为了解决这个问题，我使用 openByID 代替了 getActiveSpreadsheet，但该功能仍然无法正常工作。任何帮助将非常感激！

// Function will run automatically on a change to the spreadsheet by looking at the event object, or e
function onChange(e){ 

// Look in the log to see what type of change is represented by the event
 Logger.log(e.changeType);

// Only run the rest of the function if the type of change represented by the event object is an inserted row 
 if(e.changeType=='INSERT_ROW'){

// Get the current spreadsheet that is the source 
 var sourceSpreadsheet = SpreadsheetApp.openById('File ID');

// Get the first sheet of the current spreadsheet (0 indexed)   
 var sourceSheet = sourceSpreadsheet.getSheets()[0];

// Get the last row of the sheet that has data in it
 var lastrow = sourceSheet.getLastRow();

// Get the value on the last row, for 3 different columns
 var sourceRange1 = sourceSheet.getRange(lastrow, 1).getValues();

 var sourceRange2 = sourceSheet.getRange(lastrow, 2).getValues();  

 var sourceRange3 = sourceSheet.getRange(lastrow, 3).getValues();

// Create new destination spreadsheet
 var newSpreadsheet = SpreadsheetApp.create("My New File Name"); 

// Get the first sheet of the new spreadsheet (0 indexed)   
 var newSheet = newSpreadsheet.getSheets()[0];

// Set destination range to put data to
 var newRange = newSpreadsheet.getRange(1,1).setValues(sourceRange1)

 var newRange2 = newSpreadsheet.getRange(1,2).setValues(sourceRange2)

 var newRange3 = newSpreadsheet.getRange(1,3).setValues(sourceRange3)

}

}

标签： google-apps-scriptgoogle-sheets

解决方案

改变

var newRange = newSpreadsheet.getRange(1,1).setValues(sourceRange1);

至

var newRange = newSheet.getRange(1,1).setValues(sourceRange1);

应该可以解决您的问题。

该代码还可以优化为：

function onChange(e){ 

  Logger.log(e.changeType);

  if(e.changeType=='INSERT_ROW'){
    var sourceSpreadsheet = SpreadsheetApp.openById('File ID');
    var sourceSheet = sourceSpreadsheet.getSheets()[0];

    var newSpreadsheet = SpreadsheetApp.create("My New File Name"); 
    var newSheet = newSpreadsheet.getSheets()[0];

    var lastrow = sourceSheet.getLastRow();

    var sourceRange = sourceSheet.getRange(lastrow, 1, 1, 3).getValues();

    newSheet.getRange(1,1,1,3).setValues(sourceRange);

    }

}

参考：

推荐阅读