rough.gs

// AddReportSheet copies the current sheet to a new sheet
function AddReportSheet() {
  // Ask the user to specify the N Number for the new report.
  var referenceNumber = SpreadsheetApp.getUi()
    .prompt("Please enter the N Number.")
    .getResponseText();

  // Duplicate the template sheet.
  var newSheet = SpreadsheetApp.getActiveSpreadsheet().duplicateActiveSheet();

  // Set sheet name and SDML reference cell to reference number
  var reference = "N" + referenceNumber;
  newSheet.setName(reference);
  newSheet.getRange("D2").setValue(reference);
}

// Flatten copies only the values of the first 5 columns over to the 6th column.
function Flatten() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet
    .getRange("A:M")
    .copyTo(
      sheet.getRange("A1"),
      SpreadsheetApp.CopyPasteType.PASTE_VALUES,
      false,
    );
}

// sendReport exports sheet "Q" to a PDF format and
// then emails it to the address in L2.
function sendReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // TODO: Currently we are always sending the sheet named "Q".
  // We can send the currently active sheet by changing the next line to:
  // var sheet = ss.getActiveSheet();
  var sheet = ss.getSheetByName("Q");
  var reportRange = "C26:M76";

  var pdfBlob = exportRangeToPDF(ss, sheet, reportRange);
  var quoteName = sheet.getRange("D11").getValue();
  pdfBlob.setName("Quotation from Southern Ductwork" + quoteName);

  var to = sheet.getRange("L2").getValue();
  var subject = sheet.getRange("L3").getValue();
  var body = sheet.getRange("L4").getValue();
  var senderName = "Kieren Sines - Southern Ductwork (Manufacture) Ltd";

  MailApp.sendEmail({
    to: to,
    subject: subject,
    body: body,
    name: senderName,
    attachments: [pdfBlob],
  });
}

// saveReportToDrive exports sheet "Q" to a PDF format and
// then saves it to the users drive.
function saveReportToDrive() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // TODO: Currently we are always saving the sheet named "Q".
  // We can send the currently active sheet by changing the next line to:
  // var sheet = ss.getActiveSheet();
  var sheet = ss.getSheetByName("Q");
  var reportRange = "C26:M76";

  var pdfBlob = exportRangeToPDF(ss, sheet, reportRange);
  var quoteName = sheet.getRange("D11").getValue();
  // This name will be the name of the file in Google Drive.
  pdfBlob.setName("Quotation from Southern Ductwork" + quoteName);

  // Now we need to find the existing folder or create one.
  var destinationFolderName = "example folder";
  var rootFolder = DriveApp.getRootFolder();
  var folderList = rootFolder.getFoldersByName(destinationFolderName);
  var destinationFolder;
  if (!folderList.hasNext()) {
    // If there's no folder with that name, we can create one
    destinationFolder = rootFolder.createFolder(destinationFolderName);
  } else {
    destinationFolder = folderList.next();
  }

  destinationFolder.createFile(pdfBlob);
}

// addURLParams converts an object of parameters
// and a base URL to a full URL including those params.
// This is used by exportRangeToPDF.
function addURLParams(base, params) {
  return (
    base +
    "?" +
    Object.entries(params)
      .flatMap(([k, v]) =>
        Array.isArray(v)
          ? v.map((e) => `${k}=${encodeURIComponent(e)}`)
          : `${k}=${encodeURIComponent(v)}`,
      )
      .join("&")
  );
}

// exportRangeToPDF converts a given sheet range to a PDF formatted blob (file).
// The spreadsheet, sheet and range must be specified.
//
// This blob is returned and then can be attached to an email or
// saved to a drive.
function exportRangeToPDF(ss, sheet, range) {
  var urlBase = ss.getUrl().replace(/edit$/, "");
  var exportUrl = addURLParams(urlBase + "export", {
    exportFormat: "pdf",
    format: "pdf",
    gid: sheet.getSheetId(),
    id: ss.getId(),
    range: range,
    // paper size
    size: "A4",
    // orientation, false for landscape
    portrait: "true",
    // fit to width, false for actual size
    fitw: "true",
    //hide optional headers and footers
    sheetnames: "true",
    printtitle: "true",
    pagenumbers: "true",
    // hide gridlines
    gridlines: "false",
    // do not repeat row headers (frozen rows) on each page
    fzr: "false",
  });

  var options = {
    headers: {
      Authorization: "Bearer " + ScriptApp.getOAuthToken(),
    },
    muteHttpExceptions: true,
  };

  var response = UrlFetchApp.fetch(exportUrl, options);
  if (response.getResponseCode() !== 200) {
    console.log(
      "Error exporting Sheet to PDF!  Response Code: " +
        response.getResponseCode(),
    );
    return;
  }

  return response.getBlob();
}