javascript - Google Sheets 脚本将事件工作表下载为 csv

Question

我需要帮助。我开发了一个 GS 代码，用于将模板中的数据格式化并排列到目标表中。目标表的最终产品可以达到 100,000 行。

每次迭代后，我通常会转到"file"，然后“导出到 CSV”。

然后我突然意识到我也可以在 GS 上完成这部分工作。所以我有这个代码:

function saveAsCSV() {
var filename = "Template"; 
var folder = "C:/Downloads/"; 

var csv = "";
var v = SpreadsheetApp
        .getActiveSpreadsheet()
        .getActiveSheet()
        .getDataRange()
        .getValues();
v.forEach(function(e) {
  csv += e.join(",") + "\n";
});

我想修改这段代码，而不是它是一个函数，我希望它在它之前运行的代码之后直接自动运行。

你能告诉我哪里出错了吗？

非常感谢。

编辑:

完整代码为:

    function autoDup13() {
  var m = 13
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var LRow1 = sheet.getLastRow();
  var newData = [];
  for(var n in data){
    newData.push(data[n]);
    //if(!Number(data[n][2])){continue};// if column 3 is not a number then do nothing
    for(var c=1 ; c < m ; c++){ // start from 1 instead of 0 because we have already 1 copy
      newData.push(data[n]);//store values
    }
  }
  sheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);// write new data to sheet, overwriting old data
  sheet.insertColumnBefore(1);
  sheet.insertRows(1, 1);
  var LRow2 = SpreadsheetApp.getActiveSpreadsheet().getLastRow();
  var TemplateSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");
  var TemporarySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Temporary");
  var DestSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Destination Sheet");
  TemplateSheet.getRange("A1:DF1").copyTo(DestSheet.getRange("A1:DF1"), {contentsOnly:true});
  TemplateSheet.getRange("A2:A2").copyTo(DestSheet.getRange("A2:A"+LRow2+""), {contentsOnly:false}); //copy counter
  TemplateSheet.getRange("S2:T2").copyTo(DestSheet.getRange("S2:T"+LRow2+""), {contentsOnly:true}); //Proj Start & End dates
  TemplateSheet.getRange("BI2:BJ2").copyTo(DestSheet.getRange("BI2:BJ"+LRow2+""), {contentsOnly:false}); //copy Qual Type Data
  TemplateSheet.getRange("BQ2:BR2").copyTo(DestSheet.getRange("BQ2:BR"+LRow2+""), {contentsOnly:false}); //project and finance managers
  TemplateSheet.getRange("CM2:CR2").copyTo(DestSheet.getRange("CM2:CR"+LRow2+""), {contentsOnly:false}); //Task Numbers
  TemplateSheet.getRange("DA2:DA2").copyTo(DestSheet.getRange("DA2:DA"+LRow2+""), {contentsOnly:false}); //Last bit of tasks
  TemplateSheet.getRange("DD2:DF2").copyTo(DestSheet.getRange("DD2:DF"+LRow2+""), {contentsOnly:true}); //Finance part
  DestSheet.getRange("A1:DF"+LRow2+"").moveTo(TemporarySheet.getRange("A1:DF"+LRow2+"")); //Move Values to Temp sheet
  TemporarySheet.getRange("A1:DF1").copyTo(DestSheet.getRange("A1:DF1"), {contentsOnly:true}); //Take headers
  TemporarySheet.getRange("A2:A"+LRow2+"").copyTo(DestSheet.getRange("A2:A"+LRow2+""), {contentsOnly:true}); //Take Counter
  TemporarySheet.getRange("BI2:BJ"+LRow2+"").copyTo(DestSheet.getRange("BI2:BJ"+LRow2+""), {contentsOnly:true}); //Take qual class
  TemporarySheet.getRange("BQ2:BR"+LRow2+"").copyTo(DestSheet.getRange("BQ2:BR"+LRow2+""), {contentsOnly:true}); //take finance manager
  TemporarySheet.getRange("CM2:CR"+LRow2+"").copyTo(DestSheet.getRange("CM2:CR"+LRow2+""), {contentsOnly:true}); //Task Numbers --- test row to test true false on first cell blank
  TemporarySheet.getRange("DA2:DF"+LRow2+"").copyTo(DestSheet.getRange("DA2:DF"+LRow2+""), {contentsOnly:true}); //Last bit of tasks
  TemplateSheet.getRange("B2:B2").copyTo(DestSheet.getRange("B2:B"+LRow2+""), {contentsOnly:false}); //take Template
  TemplateSheet.getRange("G2:G2").copyTo(DestSheet.getRange("G2:G"+LRow2+""), {contentsOnly:false}); //take proj number
  TemplateSheet.getRange("H2:H2").copyTo(DestSheet.getRange("H2:H"+LRow2+""), {contentsOnly:false}); //take proj name
  TemplateSheet.getRange("K2:K2").copyTo(DestSheet.getRange("K2:K"+LRow2+""), {contentsOnly:false}); //Proj Org
  TemplateSheet.getRange("S2:T2").copyTo(DestSheet.getRange("S2:T"+LRow2+""), {contentsOnly:false}); //Dates
  Browser.msgBox('Duplicate Items Results', +LRow1+' unique rows where duplicated '+m+ ' times.\\n\\n Last row of data is at (row '+LRow2+')', Browser.Buttons.OK);
  function saveAsCSV() {
    var filename = "Template"; 
    var folder = "C:/Downloads/"; 

    var csv = "";
    var v = SpreadsheetApp
        .getActiveSpreadsheet()
        .getActiveSheet()
        .getDataRange()
        .getValues();
    v.forEach(function(e) {
    csv += e.join(",") + "\n";
});
}
}

Answer 1

只需将其放在先于其运行的函数内即可。例如:

function getSheetName(){
   var mySpreadSheet =  SpreadsheetApp.openById('MY_SPREADSHEET_ID');
   Logger.log(mySpreadSheet.getSheetName());
}

function MainFunction(){
   Logger.log("I am Batman");

   getSheetName();
}

这里我正在 Apps Scripts 脚本编辑器中执行 MainFunction。打印“我是 bat 侠”后，它执行 getSheetName() 函数。

输出:

[17-12-14 17:59:02:987 HKT] I am Batman
[17-12-14 17:59:03:077 HKT] 12345