如何使用Google Apps脚本将电子表格标题和最后一行值导出到CSV文件

看看这是否对您有帮助：

我们假设您的电子表格配置如下：

• 您有一个名为“响应”的提交表。
• 您需要一个名为Temp的脚本的帮助表，该脚本的第一行标题与“响应”表相同

在下面的示例中，您希望将标头（橙色）和最后提交的行（绿色）保存为CSV文件。

When you access the script you'll change the following:

• The Spreadsheet ID of the whole document
• The ID of the tab Temp (numbers found after edit#gid=)
• The email address of the recipient

Code:

// Example: https://docs.google.com/spreadsheets/d/1kXaTto1ktqX0XNKQ035897FGX5tV75o7wuYHiNCOqEFfI/edit#gid=1269457815
  // In this link the ID of the Spreadsheet is everything after /d/
  // which is: 1kXaTto1ktqX0XNKQ035897FGX5tV75o7wuYHiNCOqEFfI
  // THE ID of the sheet Temp would be something like: 1269457815

// ---------- Menu ----------
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('CSV File')
  .addItem('Step 1: Email CSV', 'EmailRange')
  .addItem('Step 2: Save CSV file', 'showurl')
  .addToUi();
}



// Choose how do want to export the: csv,pdf,xlsx

EXPORT_TYPE="csv";

function EmailRange() {



  // Enter Sheet ID in between ""

  var sheetId = "ID GOES HERE";
  var ss = SpreadsheetApp.openById(sheetId);
  var sheet=ss.getSheetByName("Responses");

  // You can set up the headers beforehand
  var temp = ss.getSheetByName("Temp");


  //Copy range onto Temp sheet

  var rangeToCopy = sheet.getRange("A"+(ss.getLastRow())+":N"+(ss.getLastRow()));

  // It will erase any previous data

  rangeToCopy.copyTo(temp.getRange(2, 1));


  // Temporarily hide the sheet

  ss.getSheetByName('Responses').hideSheet()


  //Authentification 
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

  var url="https://docs.google.com/spreadsheets/d/"+ss.getId()+"/export?format="+EXPORT_TYPE;

  //Fetch URL of active spreadsheet
  var fetch=UrlFetchApp.fetch(url,params);

  //Get content as blob
  var blob=fetch.getBlob(); 

  var mimetype;
  if(EXPORT_TYPE=="pdf"){
    mimetype="application/pdf";      
  }else if(EXPORT_TYPE=="csv"){
    mimetype="text/csv";    
  }else if(EXPORT_TYPE=="xlsx"){
    mimetype="application/xlsx";   
  }else{
    return;
  }

  // OP: send an alert email with column B value as a subject



    var subject = sheet.getRange("B"+(ss.getLastRow()));
  var timestamp = sheet.getRange("A"+(ss.getLastRow()));
  var Title = subject.getValues();
  var signature = timestamp.getValues();


  //Change Email Recipient underneath

  GmailApp.sendEmail('[email protected]',
                     'Job ID: '+Title, 
                     'Hi there,' + '\n\n' +  'A new entry has been submitted, please find the details in the attached CSV file.'  + '\n\n' +  'Submitted on: '+signature,
                     {
                         attachments: [{
                         fileName: Title + "."+EXPORT_TYPE,
                         content: blob.getBytes(),
                         mimeType: mimetype
                      }]
    });

//Reshow Response sheet

  ss.getSheetByName('Responses').showSheet()

}

function showurl() {

  // Enter Spreadsheet ID after d/ and the TAB ID of Temp after gid=


  var htmlOutput = HtmlService
  .createHtmlOutput('Click here <a href="https://docs.google.com/spreadsheets/d/ID GOES HERE/export?format=csv&gid=TAB ID GOES HERE">My File</a> to download')
  .setWidth(250) //optional
  .setHeight(50); //optional
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Download CSV File');

}

STEP 1: Sending an email to the defined recipient in the script with the subject being the value of the last row in column B, and the signature the submission date & time, like this:

The CSV file will be attached to the email:

STEP 2: From the CSV File Menu which is created when opening the spreadsheet:

Save file to your Local desktop: