将Google工作表转换为CSV，然后将其附加到电子邮件

Question

我有一个简单的应用程序脚本需要，但不能从社区找到一个足够近的样本。我需要从谷歌电子表格转换2张表，单独，作为CSV文件，然后在电子邮件中附加它们。到目前为止，我找到了一个将工作表转换为CSV格式并将其归档到文件夹中的脚本。我找了一个脚本来添加，而不是将CSV文件附加到电子邮件中，但找不到任何我可以使用的基于我的新手水平的应用程序脚本知识。任何帮助都将不胜感激。非常感谢。

脚本:

function sheet1ToCsv()
{
var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
var sheet_Name = "xxxx"
var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_Name)
var sheetNameId = sheet.getSheetId().toString();
params= ssID+"/export?gid="+sheetNameId +"&format=csv"
var url = "https://docs.google.com/spreadsheets/d/"+ params
var result = UrlFetchApp.fetch(url, requestData);  
var folderId = "yyyy";
var csvContent = {
title: sheet_Name+".csv",
mimeType: "application/vnd.csv",  
parents: [{id: folderId}]
}
var fileJson = Drive.Files.insert(csvContent,result)
} 

Answer 1

在您的显示脚本中，没有发送电子邮件。而且，只有一个CSV文件被创建到谷歌驱动器。从I need to convert 2 sheets from a Google Spreadsheet, individually, as CSV files then attach them in an email.和你的展示脚本来看，当你的展示脚本被修改时，下面的修改是如何进行的?

修改脚本:

function myFunction() {
var emailAddress = "###"; // Please set your email address.
var sheetNames = ["Sheet1", "Sheet2"]; // Please set your 2 sheet names you want to use.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssID = ss.getId();
var requestData = { "method": "GET", "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } };
var blobs = sheetNames.map(s => {
var params = ssID + "/export?gid=" + ss.getSheetByName(s).getSheetId() + "&format=csv";
var url = "https://docs.google.com/spreadsheets/d/" + params;
return UrlFetchApp.fetch(url, requestData).getBlob().setName(s); // <--- Modified
});
MailApp.sendEmail({ to: emailAddress, subject: "sample subject", body: "sample body", attachments: blobs });
}

• 该脚本运行时，发送邮件，包含2个CSV文件作为附件文件。

引用:

• map ()
• sendEmail(消息)

Answer 2

function myfunk() {
const folder = DriveApp.getFolderById("folderid");
const ss = SpreadsheetApp.getActive();
const names = ["Sheet1", "Sheet2"];
const params = { "method": "GET", "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } };
let bA = [];
names.forEach(n => {
let sh = ss.getSheetByName(n);
let url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/export?gid=" + sh.getSheetId() + "&format=csv";
let r = UrlFetchApp.fetch(url, params);
let csv = r.getContentText();
let file = folder.createFile(n, csv, MimeType.CSV);
bA.push(file.getBlob());
})
GmailApp.sendEmail(recipient, subject, body, { attachments: bA })
}