Example on how to export a Google sheet to various formats, includes most PDF options

exportSpreadsheet.gs

function exportSpreadsheet() {
 
  //All requests must include id in the path and a format parameter
  //https://docs.google.com/spreadsheets/d/{SpreadsheetId}/export
 
  //FORMATS WITH NO ADDITIONAL OPTIONS
  //format=xlsx       //excel
  //format=ods        //Open Document Spreadsheet
  //format=zip        //html zipped          
  
  //CSV,TSV OPTIONS***********
  //format=csv        // comma seperated values
  //             tsv        // tab seperated values
  //gid=sheetId             // the sheetID you want to export, The first sheet will be 0. others will have a uniqe ID
  
  // PDF OPTIONS****************
  //format=pdf     
  //size=0,1,2..10             paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B5  
  //fzr=true/false             repeat row headers
  //portrait=true/false        false =  landscape
  //fitw=true/false            fit window or actual size
  //gridlines=true/false
  //printtitle=true/false
  //pagenum=CENTER/UNDEFINED      CENTER = show page numbers / UNDEFINED = do not show
  //attachment = true/false      dunno? Leave this as true
  //gid=sheetId                 Sheet Id if you want a specific sheet. The first sheet will be 0. others will have a uniqe ID. 
                               // Leave this off for all sheets. 
  // EXPORT RANGE OPTIONS FOR PDF
  //need all the below to export a range
  //gid=sheetId                must be included. The first sheet will be 0. others will have a uniqe ID
  //ir=false                   seems to be always false
  //ic=false                   same as ir
  //r1=Start Row number - 1        row 1 would be 0 , row 15 wold be 14
  //c1=Start Column number - 1     column 1 would be 0, column 8 would be 7   
  //r2=End Row number
  //c2=End Column number
 
  var ssID = "12g8-tcRwFkcL7El...XdQAzSR7v8-geIR6r-IY";
  var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export"+
                                                        "?format=pdf&"+
                                                        "size=0&"+
                                                        "fzr=true&"+
                                                        "portrait=false&"+
                                                        "fitw=true&"+
                                                        "gridlines=false&"+
                                                        "printtitle=true&"+
                                                        "sheetnames=true&"+
                                                        "pagenum=CENTER&"+
                                                        "attachment=true";
                                                        
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  var response = UrlFetchApp.fetch(url, params).getBlob();
  // save to drive
  DriveApp.createFile(response);
  
  //or send as email
  /*
  MailApp.sendEmail(email, subject, body, {
        attachments: [{
            fileName: "TPS REPORT" + ".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        }]
    });};
  */
  
}

Speculatively, if you were to give a student a link, not to the sheet, but to a script that opens the sheet and plugs the current detailed time (milliseconds) to a cell, then call the refresh suggested by https://github.com/techdoneforyou. That plugging of a value will cause the sheet to recalc, then include the PDF call in the script. Another student calling would plug a different value I think (and hope) that could work! There may be better ways that you hear of. Good Luck in your efforts.

…

On Fri, Nov 4, 2022 at 4:45 PM Brandon Lee Camilleri < ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ @basetoad <https://github.com/basetoad> You may need to provide an explanation of how the sheet is 'refreshed' that causes it to generate new value Every time the spreadsheet is accessed or refreshed the values of many cells are regenerated by means of the following code: =ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX(settings!$E$2:$E$14, RANDBETWEEN(1, 13))), 1, 1) how you are intending to generate the PDF The intention is to have students click on the link to generate a pdf to then print and work academic exercises. Each pdf contains a set of exercises which change each time the spreadsheet is refreshed by means of variables (these are the earlier mentioned cells operated using the code above). ------------------------------ @techdoneforyou <https://github.com/techdoneforyou> SpreadsheetApp.flush() Is there a way to incorporate this in a link? That is, I send a clickable link to my students who are presented with a pdf ready for printing. For example, https://docs.google.com/spreadsheets/d_____________/export?format=pdf&portrait=true&size=7&gid=____________&range=A1:D16 Thank you both for helping me out. — Reply to this email directly, view it on GitHub <https://gist.github.com/78f9867a691e549c9c70#gistcomment-4358834> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AUPYMM45ACTB2IZUKZQC66TWGVYX3BFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFQKSXMYLMOVS2I5DSOVS2I3TBNVS3W5DIOJSWCZC7OBQXE5DJMNUXAYLOORPWCY3UNF3GS5DZVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVAZTAOJWGA3TKN5HORZGSZ3HMVZKMY3SMVQXIZI> . You are receiving this email because you were mentioned. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .

Thanks for your message @Yagisanatode , but i believe i have found the answer . but forgot what's the code is. i will share here once i remember.

I have been racking my brain trying to figure this out myself. I would be forever in your debt if you could post this information once you find it. I have tried several different combinations to try and get the date/time footer to show, but no luck. Thanks in advance for any assistance you can provide!

@Yagisanatode @robertcragg I did some digging across the interwebs and worked it out, hope this helps someone out there:

• printdate=true/false
• printtime=true/false
• timestamp=[0, X) where x is a non-negative number - not sure what the maximum value allowed is (I'll leave this as homework for the next contributor 😉). Required if either printdate / printtime are set to true. Value represents the "1900 Spreadsheet Date System" timestamp, based in UTC.

Some additional remarks on the timestamp

• Note that the timestamp here is the weird Sheets / Excel format (based on the historical spreadsheet "epoch")
• Google Sheets docs here suggest that the earliest date possible is (1/1/1900), but you'll observe that if you write the formula =DATE(0,1,-1) in Google Sheets, you'll get 30/12/1899. Similarly, if you format a cell with the value 0 via menu option Format > Number > Date, you'll see 30/12/1899. This matches exactly with what the export API returns.
• For those curious, some digging reveals its probably historically related to this, some commentary available here
• Some JavaScript code to help with the conversions (painful, but necessary for me working with Apps Script)

function SpreadsheetTimestampToJSDate(timestamp) {
  return new Date(Math.round((timestamp - 25569)*86400*1000));
}

function JSDateToSpreadsheetTimestamp(date) {
  return (date.valueOf() / (86400*1000)) + 25569;
}

• Note that I haven't been able to find a timezone option; by default the date is rendered in UTC. So if you want local time, you'll need to add the offset - e.g. for GMT+11:00, use SpreadsheetTimestampToJSDate(new Date(new Date().valueOf() + 11 * 3600000))

Thanks for tuning in, bye!

Does anyone know how to export a specific range in a spreadsheet as a PNG and not PDF? Many thanks in advance!

I couln't find official support documentation around the filesize limitations for blob. Anyone knows (related to GAS)?

@mertdotcc

export a specific range

  //r1=Start Row number - 1        row 1 would be 0 , row 15 wold be 14
  //c1=Start Column number - 1     column 1 would be 0, column 8 would be 7   
  //r2=End Row number
  //c2=End Column number

a spreadsheet as a PNG

Is it possible?

does anyone know a key for including/excluding notes? They seem to be included by default (opposite of what happens when you ctrl+P on google sheets).

Figured it out -- includenotes=false

Very helpful ! There's a GAS sample that provides similar info, albeit without explanations.

Would anyone know how custom page breaks are set via this API ?

pagenum can be LEFT or RIGHT too

Can anyone help point out where I can manage the PDF file name? Ideally, I'd like to incorporate the value of a cell into the file name. Don't seem to be able to find where through some trial and error. This may not be the right place. Any help is appreciated!

I have not found one. In case it helps, it seems as though the filename is a combination of Spreadsheet (Book) name, and Sheet name that is bring printed so conceptually "Spreadsheet - Sheet.pdf"

Line 53 var response = UrlFetchApp.fetch(url, params).getBlob().setName("foobar.pdf"); Steve Decker Tech Done For You

…

On Tue, May 2, 2023 at 5:37 PM jasondanielssq ***@***.***> wrote: @jasondanielssq commented on this gist. ________________________________ Can anyone help point out where I can manage the PDF file name? Ideally, I'd like to incorporate the value of a cell into the file name. Don't seem to be able to find where through some trial and error. This may not be the right place. Any help is appreciated! — Reply to this email directly, view it on GitHub or unsubscribe. You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS or Android.

@techdoneforyou - Thank you very much! That's what I needed!

Great script Spencer - just curious if you had time to reverse engineering Custom Page breaks in Google Sheets and how to export the sheets to PDFs using Custom Page Breaks. Many thanks.

I have found that this method now causes a 500 HTTP error. I've reverse engineered what is apparently an entirely new API in my gist. It exposes a lot greater control over formatting such as margins and page flow and page size. https://gist.github.com/basezen/4c76dd1ba0f4468edba6fbc24c583afd
Comments and suggestions welcome.