Skip to content

Instantly share code, notes, and snippets.

@aidotno

aidotno/exportSpreadsheet.gs

Forked from Spencer-Easton/exportSpreadsheet.gs
Created February 27, 2020 09:43
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save aidotno/a396ffb48ea918aa2f4521dfbdb1772f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save aidotno/a396ffb48ea918aa2f4521dfbdb1772f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @Spencer-Easton Spencer-Easton revised this gist Apr 28, 2016. 1 changed file with 11 additions and 13 deletions.
    24 changes: 11 additions & 13 deletions exportSpreadsheet.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -1,22 +1,20 @@
    function exportSpreadsheet() {

    //https://docs.google.com/spreadsheets/export?

    //All requests must include id and exportFormat
    //id=spreadsheetId


    //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
    //exportFormat=xlsx //excel
    //exportFormat=ods //Open Document Spreadsheet
    //exportFormat=zip //html zipped
    //format=xlsx //excel
    //format=ods //Open Document Spreadsheet
    //format=zip //html zipped

    //CSV,TSV OPTIONS***********
    //exportFormat=csv // comma seperated values
    //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****************
    //exportFormat=pdf
    //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
    @@ -38,8 +36,8 @@ function exportSpreadsheet() {
    //c2=End Column number

    var ssID = "12g8-tcRwFkcL7El...XdQAzSR7v8-geIR6r-IY";
    var url = "https://docs.google.com/spreadsheets/export?id="+ssID+
    "&exportFormat=pdf&"+
    var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export"+
    "?format=pdf&"+
    "size=0&"+
    "fzr=true&"+
    "portrait=false&"+
  2. @Spencer-Easton Spencer-Easton revised this gist Feb 9, 2016. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions exportSpreadsheet.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -24,7 +24,7 @@ function exportSpreadsheet() {
    //gridlines=true/false
    //printtitle=true/false
    //pagenum=CENTER/UNDEFINED CENTER = show page numbers / UNDEFINED = do not show
    //attachment = true/false dunno?
    //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
    @@ -35,7 +35,7 @@ function exportSpreadsheet() {
    //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 Row number
    //c2=End Column number

    var ssID = "12g8-tcRwFkcL7El...XdQAzSR7v8-geIR6r-IY";
    var url = "https://docs.google.com/spreadsheets/export?id="+ssID+
  3. @Spencer-Easton Spencer-Easton revised this gist Feb 9, 2016. 1 changed file with 14 additions and 2 deletions.
    16 changes: 14 additions & 2 deletions exportSpreadsheet.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -52,7 +52,19 @@ function exportSpreadsheet() {

    var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

    var resp = UrlFetchApp.fetch(url, params).getBlob();
    DriveApp.createFile(resp);
    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"
    }]
    });};
    */

    }
  4. @Spencer-Easton Spencer-Easton created this gist Feb 9, 2016.
    58 changes: 58 additions & 0 deletions exportSpreadsheet.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,58 @@
    function exportSpreadsheet() {

    //https://docs.google.com/spreadsheets/export?

    //All requests must include id and exportFormat
    //id=spreadsheetId

    //FORMATS WITH NO ADDITIONAL OPTIONS
    //exportFormat=xlsx //excel
    //exportFormat=ods //Open Document Spreadsheet
    //exportFormat=zip //html zipped

    //CSV,TSV OPTIONS***********
    //exportFormat=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****************
    //exportFormat=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?
    //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 Row number

    var ssID = "12g8-tcRwFkcL7El...XdQAzSR7v8-geIR6r-IY";
    var url = "https://docs.google.com/spreadsheets/export?id="+ssID+
    "&exportFormat=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 resp = UrlFetchApp.fetch(url, params).getBlob();
    DriveApp.createFile(resp);

    }