google-apps-script - 复制行并删除

Question

我有 2 张纸 - 'Form Responses 1' 和 'Students'。

当我运行下面的函数时，它将所有数据从“表单响应 1”复制到“学生”，我需要在第二张表中进一步处理数据。复制后，我希望从“表单响应 1”中删除该特定行。下一轮当我再次运行该函数时（当'Form Responses 1'中有新数据时），它将相应地附加到'Students'。

如果您能建议我如何修改下面的代码，将不胜感激，谢谢！

function Copy() 
{
    var sss = SpreadsheetApp.openById('sheet ID'); //replace with source ID
    var ss = sss.getSheetByName('Form Responses 1'); //replace with source Sheet tab name
    var range = ss.getRange('A:V'); //assign the range you want to copy
    var data = range.getValues();
    var numRows = range.getNumRows();
    var getRow = range.getRow();

    var tss = SpreadsheetApp.openById('sheet ID'); //replace with destination ID
    var ts = tss.getSheetByName('Students');  

    ts.getRange(1, 1, data.length, data[0].length).setValues(data);

    /*delete function is missing*/
} 

标签： google-apps-scriptgoogle-sheets

Answer 1

下面的代码应该可以达到预期的结果，否则它是一个很好的起点。代码很简单，所以我把所有的解释都放在了代码的注释里。

要使用此代码，您可以创建一个独立脚本。（不需要绑定到电子表格。）将主要和次要代码复制到两个单独的选项卡中。（见附图。）一旦您更改了两个脚本中的 ID，运行主脚本，您应该会看到所需的结果。（请注意，表单响应中的所有数据都将被清除。）

希望这会有所帮助，如果您不确定，请随时询问。请注意，此时我将引导您查看文档，而不是尝试解释某些方法/调用的工作原理：

主要代码

function copy_Delete(){

var ss, sheet;
var lastRow, lastColumn;
var responseData;

//Get Spreadsheet and Sheet name for Form Responses Sheet  
ss = SpreadsheetApp.openById("YOUR_ID_HERE");
sheet = ss.getSheetByName("Form Responses 1");

//Gets a numerical value representing the last row and last column where data is entered.  
lastRow = sheet.getLastRow();
lastColumn = sheet.getLastColumn();

//Get all the data in the From Responses Sheet and put it all in an array.
responseData = sheet.getRange(2, 1, lastRow -1, lastColumn).getValues();

//Call the destination function and pass the array to it. Then clear form responses of all data.
destination(responseData);
sheet.clear();
}

二级代码

    function destination(responseData){

    var ss, sheet, form;
    var numRows, numColumns, lastRow;

    //Get Spreadsheet and Sheet name for your destination sheet 
    ss = SpreadsheetApp.openById("YOUR ID HERE");
    sheet = ss.getSheetByName("Destination Sheet");

    //Get the dimensions of the array and the last row of the spreadsheet.
    numRows = responseData.length;
    numColumns = responseData[0].length;
    lastRow = sheet.getLastRow();

    //Using the dimensions above,we push our array from the previous function into our new sheet.
    sheet.getRange(lastRow +1, 1, numRows, numColumns).setValues(responseData);

    //Get form and delete all responses.
    form = FormApp.openById('YOUR_FORM_ID_HERE');
    form.deleteAllResponses();
    }

附例