Export a google sheet to CSV and email as attachment -> sends a CSV attachment but is populated with junk

Question

I'm attempting to export a specific sheet to CSV format and then email the csv file as an attachment in email. My code ends up emailing a CSV attachment okay, but the content of the csv attachment is bogus. I log the URL and then test this directly from the browser. It seems to find the correct named sheet. Is there a way to verify the result of UrlFetchApp.fetch()? Can anyone advise what I'm doing wrong here?

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var convertedSheet = ss.getSheetByName(dateToProcess);
  if (convertedSheet != null) {
  
    var sheetName = convertedSheet.getName();
    Logger.log("Sheet Name:", sheetName); // logs correct sheet name
    
    // this approach to constructing url is suggested in a similar post instead of using "getUrl()
    var url = 'https://docs.google.com/spreadsheets/d/'+ SpreadsheetApp.getActiveSpreadsheet().getId();
    url += '#gid=';
    url += convertedSheet.getSheetId();
    url += "/export?exportFormat=csv&format=csv";
    
    Logger.log("URL: ", url); //this brings up correct sheet when I paste the url in the browser. It does not trigger a  download of it in the browser - should it be?

    var email = Session.getActiveUser().getEmail();
    Logger.log(email); //correct

    var subject = "New Submission is Attached! ";
    var body = "You have a new submission!";

    var requestData = {method:"GET",headers:{"authorization":"Bearer "+        ScriptApp.getOAuthToken()}};
        
    var result = UrlFetchApp.fetch(url, requestData).getBlob();

    MailApp.sendEmail(email,
                      subject, 
                      body,
                      {
                          attachments: [{
                          fileName: sheetName + ".csv",
                          content: result.getBytes(),
                          mimeType: "text/csv"
                        }]
                      });
    
    }

In the CSV that is sent as an attachment, the cells are populated with what appears to be generated code (delimited by all the commas in the code).

Here is the basic format of the URL (minus id's and domains) ... h t t p s / / docs.google.com/spreadsheets/d//edit?ouid=&urlBuilderDomain=.org#gid=/export?exportFormat=csv&format=csv" — RLSDev, Aug 21 '20 at 21:07
Does this help? https://stackoverflow.com/questions/13238548/blob-attachments-in-mailapp-sendemail — pilchard, Aug 21 '20 at 21:13
What about the other solutions? https://stackoverflow.com/a/63491497 — TheMaster, Aug 21 '20 at 21:48
Ignore first comment above... After integrating suggested ways to format the url (not using 'getUrl', suggested in a previous post), I get this format which works from my browser: h t t p s : //docs.google.com/spreadsheets/d/SPREADSHEET_ID#gid=SHEET_ID/export?exportFormat=csv&format=csv — RLSDev, Aug 21 '20 at 22:30
Thank you for replying. I deeply apologize for the inconvenience and the time difference in my area. I noticed that your issue was resolved. I'm glad for it. In this case, I think that you could resolve by your own skill. So I would like to delete my answer, because I don't want to confuse other users. — Tanaike, Aug 22 '20 at 23:07

score 1 · Accepted Answer · answered Aug 22 '20 at 18:51

1

My problem was solved by adding calls to SpreadsheetApp.flush() between api calls that run asynchronously.

answered Aug 22 '20 at 18:51

RLSDev

23
6

score 1 · Answer 2 · edited Jan 25 '22 at 13:35

Attempted in Jan 2022, including flush(), and it wasn't working for me.

My solution was to simply change the URL format to:

var url = 'https://docs.google.com/spreadsheets/d/' + SpreadSheetId + "/export?format=csv&gid=" + SheetId;

Where

SpeadSheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
SheetId = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of your sheet").getSheetId();

Export a google sheet to CSV and email as attachment -> sends a CSV attachment but is populated with junk

2 Answers2