Convert Google Sheet to CSV then attach it to an email-CodePudding

I have a simple Apps Script need, but can't find a near enough sample from the community. I need to convert 2 sheets from a Google Spreadsheet, individually, as CSV files then attach them in an email. So far, I found a script to convert a sheet into a CSV format and file it in a folder. I looked for a script to add that will instead attach the CSV file to an email, but can't find anything which I can use based on my novice level of Apps Script knowledge. Any help will be greatly appreciated. Thank you very much.

Script:

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)

}

CodePudding user response：

In your showing script, an email is not sent. And, only one CSV file is created to Google Drive. From I need to convert 2 sheets from a Google Spreadsheet, individually, as CSV files then attach them in an email. and your showing script, when your showing script is modified, how about the following modification?

Modified script:

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 });
}

When this script is run, an email is sent by including 2 CSV files as the attachment files.

References:

CodePudding user response：

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 })
}