This script that automates the transition from a Google Sheet to a formatted Google Doc. Here is a breakdown of what this solution does:
Custom Menu Integration: The script adds a Custom Menu directly into your Google Sheet interface. With a single click, you can trigger processes like fetching data or generating reports, making the tool incredibly easy to use without touching the code.
This example is based on this URL : https://www.planetpython.org/opml.xml.
Intelligent Data Processing: It doesn't just copy text; it parses RSS and Atom feeds, extracts titles and links, and prepares them for your document. It intelligently handles the HYPERLINK formulas you use in Sheets, translating them into real, clickable links in your final document.
Automatic Folder Management: One of the most useful features of this script is its ability to keep your Drive organized. Instead of cluttering your root directory, the script automatically identifies the folder where your source spreadsheet is located and creates the new document right there.
Built-in Troubleshooting: I have included logging functionality throughout the code. If a link is malformed or a feed fails to load, the script logs exactly what happened, allowing you to debug issues quickly via the Apps Script Dashboard.
Let's see the source code for Google Apps Scripts:
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Custom Menu')
.addItem('Read OPML PlanetPython', 'fetchAndProcessFeed')
.addItem('Download Selected Content', 'downloadSelectedContentWrapper')
.addItem('Send Sheet via Email', 'sendSheetViaEmail')
.addToUi();
}
function fetchAndProcessFeed() {
const dateTime = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd_HH-mm");
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let sheet = spreadsheet.getSheetByName(dateTime);
if (!sheet) {
sheet = spreadsheet.insertSheet(dateTime);
} else {
sheet.clear();
}
sheet.appendRow(["Date", "Source", "Title", "Select"]);
const opmlUrl = "https://www.planetpython.org/opml.xml";
const opmlContent = UrlFetchApp.fetch(opmlUrl).getContentText();
const opmlDoc = XmlService.parse(opmlContent);
const outlines = opmlDoc.getRootElement().getChild("body").getChildren("outline");
outlines.forEach(outline => {
const text = outline.getAttribute("text")?.getValue().replace(/"/g, '""').replace(/[<>&]/g, '') || "";
const xmlUrl = outline.getAttribute("xmlUrl")?.getValue() || "";
try {
const feedContent = UrlFetchApp.fetch(xmlUrl).getContentText();
const feedDoc = XmlService.parse(feedContent);
const root = feedDoc.getRootElement();
if (root.getName() === "rss") {
processRssFeed(root, text, xmlUrl, sheet);
} else if (root.getName() === "feed") {
processAtomFeed(root, text, xmlUrl, sheet);
}
} catch (e) {
Logger.log(`Error processing feed ${text} (${xmlUrl}): ${e}`);
}
});
}
function processRssFeed(root, source, xmlUrl, sheet) {
const items = root.getChild("channel")?.getChildren("item") || [];
items.forEach(item => {
const title = item.getChild("title")?.getValue().replace(/"/g, '""').replace(/[<>&]/g, '') || "";
const pubDate = item.getChild("pubDate")?.getValue() || "";
const link = item.getChild("link")?.getValue().replace(/[<>&]/g, '') || xmlUrl;
sheet.appendRow([
pubDate,
`=HYPERLINK("${xmlUrl}", "${source}")`,
`=HYPERLINK("${link}", "${title}")`,
""
]);
});
}
function processAtomFeed(root, source, xmlUrl, sheet) {
const entries = root.getChildren("entry") || [];
entries.forEach(entry => {
const title = entry.getChild("title")?.getValue().replace(/"/g, '""').replace(/[<>&]/g, '') || "";
const updated = entry.getChild("updated")?.getValue() || "";
const link = entry.getChild("link")?.getAttribute("href")?.getValue().replace(/[<>&]/g, '') || xmlUrl;
sheet.appendRow([
updated,
`=HYPERLINK("${xmlUrl}", "${source}")`,
`=HYPERLINK("${link}", "${title}")`,
""
]);
});
}
function downloadSelectedContentWrapper() {
downloadSelectedContent();
}
async function downloadSelectedContent() {
const dateTime = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd_HH-mm");
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getActiveSheet();
const data = sheet.getDataRange().getValues();
const doc = DocumentApp.create(`Content_${dateTime}`);
const folderId = DriveApp.getFileById(spreadsheet.getId()).getParents().next().getId();
DriveApp.getFileById(doc.getId()).moveTo(DriveApp.getFolderById(folderId));
let contentAdded = false;
const body = doc.getBody();
for (let i = 1; i < data.length; i++) {
const row = data[i];
if (row[3] == 1) {
const sourceMatch = row[1].toString().match(/^=HYPERLINK\("([^"]+)",\s*"([^"]+)"\)$/);
const source = sourceMatch ? sourceMatch[2] : row[1].toString().replace(/[<>&]/g, '') || "Unknown source";
const titleMatch = row[2].toString().match(/^=HYPERLINK\("([^"]+)",\s*"([^"]+)"\)$/);
const title = titleMatch ? titleMatch[2] : row[2].toString().replace(/[<>&]/g, '');
const link = titleMatch ? titleMatch[1] : "";
Logger.log(`Processing ${title} with link: ${link}`);
try {
body.appendParagraph(source).setHeading(DocumentApp.ParagraphHeading.HEADING1);
const titleParagraph = body.appendParagraph(title).setHeading(DocumentApp.ParagraphHeading.HEADING2);
if (link) titleParagraph.setLinkUrl(link); // Titlu ca hyperlink
body.appendParagraph(""); // Spațiu între intrări
contentAdded = true;
Logger.log(`Added ${title} with link`);
} catch (e) {
Logger.log(`Error processing ${title} (${link}): ${e}`);
body.appendParagraph(source).setHeading(DocumentApp.ParagraphHeading.HEADING1);
const titleParagraph = body.appendParagraph(title).setHeading(DocumentApp.ParagraphHeading.HEADING2);
if (link) titleParagraph.setLinkUrl(link);
body.appendParagraph("");
contentAdded = true;
}
}
}
if (!contentAdded) {
Logger.log("No content was added to the document.");
body.appendParagraph("No content was selected or available.");
}
}
function sendSheetViaEmail() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getActiveSheet();
const sheetName = sheet.getName();
const data = sheet.getDataRange().getValues();
let htmlContent = `
<html>
<head>
<title>${sheetName}</title>
</head>
<body>
<h1>${sheetName}</h1>
<table border="1">
<tr>
<th>Date</th>
<th>Source</th>
<th>Title</th>
<th>Select</th>
</tr>
`;
data.forEach((row, index) => {
if (index > 0) {
const date = row[0] || "";
const sourceMatch = row[1].toString().match(/^=HYPERLINK\("([^"]+)",\s*"([^"]+)"\)$/);
const sourceUrl = sourceMatch ? sourceMatch[1] : "";
const sourceText = sourceMatch ? sourceMatch[2] : row[1].toString().replace(/[<>&]/g, '');
const titleMatch = row[2].toString().match(/^=HYPERLINK\("([^"]+)",\s*"([^"]+)"\)$/);
const titleUrl = titleMatch ? titleMatch[1] : "";
const titleText = titleMatch ? titleMatch[2] : row[2].toString().replace(/[<>&]/g, '');
const select = row[3] || "";
htmlContent += `
<tr>
<td>${date}</td>
<td><a href="${sourceUrl}">${sourceText}</a></td>
<td><a href="${titleUrl}">${titleText}</a></td>
<td>${select}</td>
</tr>
`;
}
});
htmlContent += `
</table>
</body>
</html>
`;
const mimetype = "application/epub+zip";
const containerXml = `
<?xml version="1.0"?>
<container version="1.0" xmlns="urn:oasis:names:tc:opendocument:xmlns:container">
<rootfiles>
<rootfile full-path="OEBPS/content.opf" media-type="application/oebps-package+xml"/>
</rootfiles>
</container>
`;
const contentOpf = `
<?xml version="1.0" encoding="UTF-8"?>
<package xmlns="http://www.idpf.org/2007/opf" unique-identifier="bookid" version="2.0">
<metadata xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:opf="http://www.idpf.org/2007/opf">
<dc:title>${sheetName}</dc:title>
<dc:identifier id="bookid">urn:uuid:${Utilities.getUuid()}</dc:identifier>
<dc:language>en</dc:language>
</metadata>
<manifest>
<item id="content" href="content.html" media-type="application/xhtml+xml"/>
</manifest>
<spine>
<itemref idref="content"/>
</spine>
</package>
`;
const blobs = [
Utilities.newBlob("mimetype", "text/plain", "mimetype").setContentType(mimetype),
Utilities.newBlob(containerXml, "application/xml", "META-INF/container.xml"),
Utilities.newBlob(contentOpf, "application/xml", "OEBPS/content.opf"),
Utilities.newBlob(htmlContent, "application/xhtml+xml", "OEBPS/content.html")
];
const zipOutput = Utilities.zip(blobs, `Sheet_${sheetName}.epub`);
MailApp.sendEmail({
to: "catalinfest@gmail.com,catafest@yahoo.com",
subject: `Sheet activ: ${sheetName}`,
body: "Vezi atașat sheet-ul activ în format EPUB.",
attachments: [zipOutput]
});
}
