Automatic export excel file from google sheets and attached mail by app script

[Roshan Kumar]

Dear Team,

Can anybody help how we automatic export excel file from google sheets and attached mail by app script.

Basically we extract specific sheets from master file from google sheets through Importrange function and we want exported excel file only for value not formulas like that IFERROR(@__xludf.DUMMYFUNCTION("IMPORTRANGE(""1gRfS-6zz3eHltWjq6yE3xpHeYjc8Sg9U1ZwOrmxQuvw"",""Dispatch List!A1:Z2000"") and we have write script only for value in google sheet copy.

We have confused how we export value only in excel file and mail automatic attached by app script, If any body help we highly appreciated.

My script code is here for reference :-

function myFunction() {

  var spreadsheetId = "1kCUuu-BdcChTzUAQN5zk-FDQQV4RT8_R2czXYEq3LLQ"; // Please set the source Spreadsheet ID.

  var destFolderId = "1-Br5Rdctt5Qy-0L7oE5B-JLLk6jmfCLE";  // Please set the destination folder ID.

  // Copy each sheet in the source Spreadsheet by removing the formulas as the temporal sheets.

  var ss = SpreadsheetApp.openById(spreadsheetId);

  var tempSheets = ss.getSheets().map(function(sheet) {

    var dstSheet = sheet.copyTo(ss).setName(sheet.getSheetName() + "_temp");

    var src = dstSheet.getDataRange();

    src.copyTo(src, {contentsOnly: true});

    return dstSheet;

  });

  

  // Copy the source Spreadsheet.

  var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());

  

  // Delete the temporal sheets in the source Spreadsheet.

  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});

  

  // Delete the original sheets from the copied Spreadsheet and rename the copied sheets.

  destination.getSheets().forEach(function(sheet) {

    var sheetName = sheet.getSheetName();

    

    if (sheetName.indexOf("_temp") == -1) {

      destination.deleteSheet(sheet);

    } else {

      sheet.setName(sheetName.slice(0, -5));

    }

  });

  // Move file to the destination folder.

  var file = DriveApp.getFileById(destination.getId());

  DriveApp.getFolderById(destFolderId).addFile(file);

  file.getParents().next().removeFile(file);

  var date = Utilities.formatDate(new Date(),"GMT","dd-MMMM-yyyy")

  

   var message = {

    to: " roshan...@gmail.com  ",

    cc: "roshan...@gmail.com",

    bcc: "",

    subject: "VB | Stocks & Dispatch Report as on " + date,

    body: "Hi Team,\n\nPlease find attached the VB | Stocks & Dispatch Report " + date + "\n\nRegards,\nRoshan Kumar",

    name: "Roshan Kumar",

    attachments: [SpreadsheetApp.openById("1kCUuu-BdcChTzUAQN5zk-FDQQV4RT8_R2czXYEq3LLQ.xlsx")]

    

   }

   MailApp.sendEmail(message);

 

   } 

Thanks & Regards

Roshan Kumar

[roshan kumar]

Hello Team,

Can anybody help, we are highly appreciated for your warm help and supports.

Thanks & Regards

Roshan Kumar

[roshan kumar]

Hello Team,

Can anybody help, we are still waiting for your positive response.

Thanks & Regards

Roshan Kumar

[Roshan Kumar]

Hello Team,

Please help & resolve my query, we will highly appreciated if anybody resolve my query.

Thanks & Regards

Roshan Kumar

[Waqar Ahmad]

You can follow this guide.

https://spreadsheet.dev/comprehensive-guide-export-google-sheets-to-pdf-excel-csv-apps-script

[Nerio Villalobos]

To export only the values and not the formulas of a sheet in a Google Sheets file and attach it to an email using Apps Script, you can modify your existing code as follows:

function exportAndSendEmail() {

  var spreadsheetId = "1kCUuu-BdcChTzUAQN5zk-FDQQV4RT8_R2czXYEq3LLQ"; // Please set the source Spreadsheet ID.

  var sheetName = "Sheet1"; // Please set the name of the sheet you want to export.

  var destFolderId = "1-Br5Rdctt5Qy-0L7oE5B-JLLk6jmfCLE"; // Please set the destination folder ID.

  var date = Utilities.formatDate(new Date(), "GMT", "dd-MMMM-yyyy");

  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var values = range.getValues();
  var newSheet = SpreadsheetApp.create("Temp Sheet");
  newSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
  var blob = newSheet.getBlob().setName(sheetName + " - " + date + ".xlsx");
  DriveApp.getFolderById(destFolderId).createFile(blob);

  var message = {
    to: "roshan...@gmail.com",
    cc: "roshan...@gmail.com",
    bcc: "",
    subject: "VB | Stocks & Dispatch Report as on " + date,
    body: "Hi Team,\n\nPlease find attached the VB | Stocks & Dispatch Report " + date + "\n\nRegards,\nRoshan Kumar",
    name: "Roshan Kumar",

    attachments: [blob]
  };
  MailApp.sendEmail(message);
  DriveApp.getFileById(newSheet.getId()).setTrashed(true);
}

In this modified code, we first specify the ID of the source Google Sheets file, the name of the sheet we want to export, and the ID of the destination folder where we want to save the exported file.

Then, we get the range of the sheet, retrieve its values only, and create a new temporary sheet. We set the values of the new sheet to the retrieved values and create a blob of the sheet in .xlsx format. We give the blob a name based on the name of the sheet and the current date.

Next, we save the blob to the destination folder, create an email message, and attach the blob to the email. Finally, we send the email and delete the temporary sheet from the Google Drive.

Please note that this code only exports and attaches one sheet at a time. If you want to export multiple sheets, you will need to modify the code accordingly.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1b4e2cb8-83f5-4b6a-92bf-d1f16ac2e7c0n%40googlegroups.com.

--

__________________________

Nerio Enrique Villalobos Morillo

+54 9 11 68851063 

Buenos Aires, Argentina

[Nerio Villalobos]

To export multiple worksheets from a Google Sheets file, you can modify the existing script to loop through each sheet in the file and export it to the destination folder. Here's an example code:

function exportAndSendEmail() {
  var spreadsheetId = "1kCUuu-BdcChTzUAQN5zk-FDQQV4RT8_R2czXYEq3LLQ"; // Please set the source Spreadsheet ID.

  var sheetNames = ["Sheet1", "Sheet2", "Sheet3"]; // Please set the names of the sheets you want to export.

  var destFolderId = "1-Br5Rdctt5Qy-0L7oE5B-JLLk6jmfCLE"; // Please set the destination folder ID.
  var date = Utilities.formatDate(new Date(), "GMT", "dd-MMMM-yyyy");

  var attachments = [];
  for (var i = 0; i < sheetNames.length; i++) {
    var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetNames[i]);

    var range = sheet.getDataRange();
    var values = range.getValues();
    var newSheet = SpreadsheetApp.create("Temp Sheet");
    newSheet.getRange(1, 1, values.length, values[0].length).setValues(values);

    var blob = newSheet.getBlob().setName(sheetNames[i] + " - " + date + ".xlsx");
    DriveApp.getFolderById(destFolderId).createFile(blob);
    attachments.push(blob);
    DriveApp.getFileById(newSheet.getId()).setTrashed(true);

  }

  var message = {
    to: "roshan...@gmail.com",
    cc: "roshan...@gmail.com",
    bcc: "",
    subject: "VB | Stocks & Dispatch Report as on " + date,
    body: "Hi Team,\n\nPlease find attached the VB | Stocks & Dispatch Report " + date + "\n\nRegards,\nRoshan Kumar",
    name: "Roshan Kumar",

    attachments: attachments
  };
  MailApp.sendEmail(message);
}

In this modified script, you can set the names of the sheets you want to export in the sheetNames array. The script will loop through each sheet, export it to the destination folder, and add the resulting blob to an attachments array. Finally, the script sends an email with all the attachments.

El sáb, 15 abr 2023 a la(s) 14:57, Roshan Kumar (roshan...@gmail.com) escribió:

Dear Nerio,

Thanks for your response but we want export multiple worksheets at a time from google sheets, So how can we do this.

Thanks & Regards

Roshan Kumar

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ebf69222-d68f-4728-ac8e-9da87acfe78fn%40googlegroups.com.