Saving a Google Sheet as epub

Question

I have a Google Sheet with content in say Col 1. One sentence in each row. I am looking for a script which can save the Col 1 as 'epub' with each sentence (in row) as a new page.

Answer 1

You need to enable Advanced Drive API

function makeEPUB() {
    const ss = SpreadsheetApp.getActive();
    const sh = ss.getSheetByName('Sheet1');
    const rg = sh.getRange(1,1,sh.getLastRow(),1);
    const vs = rg.getDisplayValues().flat();//get rows
    const document = DocumentApp.create('mydoc');//creat doc
    let body = document.getBody();
    vs.forEach(s =>{body.appendParagraph(s);body.appendPageBreak();});//append sentences and page breaks
    document.saveAndClose();
    let exportLink = Drive.Files.get(document.getId()).exportLinks["application/epub+zip"];
    let response = UrlFetchApp.fetch(exportLink, {headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}});
    let file = DriveApp.createFile(response.getBlob());
    file.setName(document.getName() + ".epub");
}

Mostly Copied from Amit Agarwal

Don't know if it works. Have no way that I know of to test it.

Answer 2

I believe your current situation and your goal as follows.

• In your Spreadsheet, there are the sentences in each row of the column "A".
• You want to retrieve a value from a cell of column "A" and convert it as a file of EPUB on your Google Drive.
• You want to achieve this using Google Apps Script.

In this case, I would like to propose the following flow.

1. Retrieve the values from the column "A" of the Spreadsheet.
2. Create Google Document as the temporal file.
3. Copy the values to Google Document.
4. Export Google Document as EPUB of application/epub+zip and save it as a file on Google Drive.
5. Remove the temporal file.

When above flow is reflected to the script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet you want to use. And, please run myFunction. By this, the values are retrieved from the cells "A1:A" and create EPUB files using the values of each row.

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const folderId = "root"; // Please set the folder ID you want to export the EPUB files. In the case of "root", the files are created to the root folder.
  
  // 1. Retrieve the values from the column "A" of the Spreadsheet.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const startRow = 1;
  const endRow = sheet.getLastRow();
  const values = sheet.getRange(`A${startRow}:A${endRow}`).getDisplayValues();

  // 2. Create Google Document as the temporal file.
  const tempDoc = DocumentApp.create("temp");
  const id = tempDoc.getId();
  const url = "https://docs.google.com/feeds/download/documents/export/Export?exportFormat=epub&id=" + id;
  const params = {headers: {authorization: `Bearer ${ScriptApp.getOAuthToken()}`}};
  const folder = DriveApp.getFolderById(folderId || "root");

  const ids = values.map(([a], i) => {
    // 3. Copy the values to Google Document.
    const filename = `rowNumber${i + 1}`;
    const doc = DocumentApp.openById(id).setName(filename);
    doc.getBody().clear().appendParagraph(a);
    doc.saveAndClose();

    // 4. Export Google Document as EPUB of `application/epub+zip` and save it as a file on Google Drive.
    const blob = UrlFetchApp.fetch(url, params).getBlob().setName(`${filename}.epub`);
    return folder.createFile(blob).getId();
  });
  console.log(ids); // Here, you can see the file IDs of the created EPUB files at the log.

  // 5. Remove the temporal file.
  DriveApp.getFileById(id).setTrashed(true);
}

• In this sample script, the filename is rowNumber${i + 1}. So, the created filename is like rowNumber1.epub, rowNumber2.epub. If you want to change this, please modify above script.
• The endpoint of const url = "https://docs.google.com/feeds/download/documents/export/Export?exportFormat=epub&id=" + id; is from exportLinks of the method of "Files: get" of Drive API. Ref

Note:

• In this case, when a lot of rows are existing in your Spreadsheet, the process time might be over the maximum execution time of 6 minutes. Please be careful this. If the process time is over the maximum execution time, please modify the values of startRow and endRow.

• If an error related to Drive API occurs, please enable Drive API at Advanced Google servicves.

• If you want to convert the values of the column "A" as one EPUB file, you can also use the following script.

    function myFunction2() {
      const sheetName = "Sheet1";
      const folderId = "root"; // Please set the folder ID you want to export the EPUB files. In the case of "root", the files are created to the root folder.
      const filename = `sampleFile`; // Please set the output filename.
  
      // 1. Retrieve the values from the column "A" of the Spreadsheet.
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
      const startRow = 1;
      const endRow = sheet.getLastRow();
      const values = sheet.getRange(`A${startRow}:A${endRow}`).getDisplayValues();
  
      // 2. Create Google Document as the temporal file.
      const tempDoc = DocumentApp.create(filename);
  
      // 3. Copy the values to Google Document.
      tempDoc.getBody().clear().appendParagraph(values.flat().join("\n"));
      tempDoc.saveAndClose();
      const id = tempDoc.getId();
  
      // 4. Export Google Document as EPUB of `application/epub+zip` and save it as a file on Google Drive.
      const url = "https://docs.google.com/feeds/download/documents/export/Export?exportFormat=epub&id=" + id;
      const params = {headers: {authorization: `Bearer ${ScriptApp.getOAuthToken()}`}};
      const folder = DriveApp.getFolderById(folderId || "root");
      const blob = UrlFetchApp.fetch(url, params).getBlob().setName(`${filename}.epub`);
      const createdFileId = folder.createFile(blob).getId();
      console.log(createdFileId); // Here, you can see the file ID of the created EPUB file at the log.
  
      // 5. Remove the temporal file.
      DriveApp.getFileById(id).setTrashed(true);
    }
  

References:

• Spreadsheet Service
• Class UrlFetchApp
• Drive Service