Create a copy of the spreadsheet template below and read the instructions
<aside> <img src="/icons/attachment_gray.svg" alt="/icons/attachment_gray.svg" width="40px" />
now with monthly and yearly wrap ups courtesy of @jasminesbindery!
</aside>
<aside> <img src="/icons/attachment_gray.svg" alt="/icons/attachment_gray.svg" width="40px" />
</aside>
<aside> <img src="/icons/attachment_gray.svg" alt="/icons/attachment_gray.svg" width="40px" />
Linking a Google Form Instructions (optional)
*for easy logging of fics in real time as you finish reading them
</aside>
UPDATED CODE:
Updating the code if you already have a working spreadsheet: Open up the Google Apps Script (via Extensions menu), delete all code and replace with the updated code below → Save. ****Easily copy all the code below via button in upper right corner. See my stories highlight for a video tutorial.
Updated 1/1/26 - improved error handling
/**
* Fetches the AO3 page (with view_adult=true), extracts data from the HTML, and writes it to the sheet.
*/
function fetchAo3Data() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const lastRow = sheet.getLastRow();
// Allow both AO3 domains (no need to treat /chapters differently)
const allowedPrefixes = [
"<https://archiveofourown.org/works/>",
"<https://archive.transformativeworks.org/works/>"
];
for (let i = 2; i <= lastRow; i++) {
let url = sheet.getRange(i, 2).getValue().trim();
if (!url || sheet.getRange(i, 3).getValue()) continue;
// Validate against allowed domains
const isAllowed = allowedPrefixes.some(prefix => url.startsWith(prefix));
if (!isAllowed) {
sheet.getRange(i, 3).setValue("Invalid URL");
continue;
}
// Append view_adult=true to bypass content warnings.
if (!url.includes("view_adult=true")) {
url += (url.includes("?") ? "&" : "?") + "view_adult=true";
}
try {
// Delay to avoid rate-limiting.
Utilities.sleep(5000);
// Fetch with custom headers and muteHttpExceptions to capture full response.
const response = UrlFetchApp.fetch(url, {
muteHttpExceptions: true,
headers: {
"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36"
}
});
const responseCode = response.getResponseCode();
// For errors other than 525, stop processing.
if (responseCode !== 200 && responseCode !== 525) {
Logger.log(`Row ${i}: Received response code ${responseCode}`);
sheet.getRange(i, 3).setValue(`Error: ${responseCode}`);
continue;
}
const html = response.getContentText();
// Extract data directly from HTML.
const data = {
title: "N/A",
author: "N/A",
relationshipTag: "N/A",
wordCount: "N/A",
additionalTags: "N/A",
fandoms: "N/A"
};
let match, matches;
// --- New format extraction ---
match = html.match(/<h4[^>]*class=["']heading["'][^>]*>\\s*<a[^>]*>(.*?)<\\/a>/);
data.title = match ? match[1].trim() : data.title;
match = html.match(/<a[^>]*rel=["']author["'][^>]*>(.*?)<\\/a>/);
data.author = match ? match[1].trim() : data.author;
match = html.match(/<li[^>]*class=["']relationships["'][^>]*>[\\s\\S]*?<a[^>]*>(.*?)<\\/a>/);
data.relationshipTag = match ? match[1].trim() : data.relationshipTag;
match = html.match(/<dd[^>]*class=["']words["'][^>]*>([\\d,]+)<\\/dd>/);
data.wordCount = match ? match[1].trim() : data.wordCount;
matches = [...html.matchAll(/<li[^>]*class=["']freeforms["'][^>]*>[\\s\\S]*?<a[^>]*>(.*?)<\\/a>/g)];
data.additionalTags = matches.length ? matches.map(m => m[1].trim()).join(", ") : data.additionalTags;
match = html.match(/<h5[^>]*class=["']fandoms\\s+heading["'][^>]*>([\\s\\S]*?)<\\/h5>/);
if (match) {
matches = [...match[1].matchAll(/<a[^>]*>(.*?)<\\/a>/g)];
data.fandoms = matches.length ? matches.map(m => m[1].trim()).join(", ") : data.fandoms;
}
// --- Fallback (old format) ---
if (data.title === "N/A") {
match = html.match(/<h2[^>]*class=["']title\\s+heading["'][^>]*>([\\s\\S]*?)<\\/h2>/);
if (match) data.title = match[1].replace(/<[^>]+>/g, "").trim();
}
if (data.relationshipTag === "N/A") {
match = html.match(/<dd[^>]*class=["']relationship\\s+tags["'][^>]*>([\\s\\S]*?)<\\/dd>/);
if (match) {
const inner = match[1].match(/<a[^>]*>(.*?)<\\/a>/);
data.relationshipTag = inner ? inner[1].trim() : data.relationshipTag;
}
}
if (data.additionalTags === "N/A") {
match = html.match(/<dd[^>]*class=["']freeform\\s+tags["'][^>]*>([\\s\\S]*?)<\\/dd>/);
if (match) {
matches = [...match[1].matchAll(/<a[^>]*>(.*?)<\\/a>/g)];
data.additionalTags = matches.length ? matches.map(m => m[1].trim()).join(", ") : data.additionalTags;
}
}
if (data.fandoms === "N/A") {
match = html.match(/<dd[^>]*class=["']fandom\\s+tags["'][^>]*>([\\s\\S]*?)<\\/dd>/);
if (match) {
matches = [...match[1].matchAll(/<a[^>]*>(.*?)<\\/a>/g)];
data.fandoms = matches.length ? matches.map(m => m[1].trim()).join(", ") : data.fandoms;
}
}
// For response code 525, only record the error if no valid data was extracted.
if (responseCode === 525 && data.title === "N/A") {
sheet.getRange(i, 3).setValue(`Error: ${responseCode}`);
} else {
// Write the extracted data back to the sheet.
sheet.getRange(i, 3).setValue(data.title);
sheet.getRange(i, 4).setValue(data.author);
sheet.getRange(i, 5).setValue(data.relationshipTag);
sheet.getRange(i, 6).setValue(data.wordCount);
sheet.getRange(i, 7).setValue(data.additionalTags);
sheet.getRange(i, 8).setValue(data.fandoms);
}
} catch (error) {
sheet.getRange(i, 3).setValue("Error fetching data");
Logger.log(`Row ${i}: ${error}`);
}
}
}
For swoony books - code also pulls Rating and Category into columns I and J, cannot be used with form
/**
* Fetches the AO3 page (with view_adult=true), extracts data from the HTML,
* and writes it to the sheet.
*/
function fetchAo3Data() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
const lastRow = sheet.getLastRow();
const allowedPrefixes = [
"<https://archiveofourown.org/works/>",
"<https://archive.transformativeworks.org/works/>"
];
function decodeHtml(str) {
if (!str) return str;
return str
.replace(/&/g, "&")
.replace(/</g, "<")
.replace(/>/g, ">")
.replace(/"/g, "\\"")
.replace(/'/g, "'")
.replace(/'/g, "'")
.replace(///g, "/")
.replace(/ /g, " ")
.trim();
}
// Check whether row already contains any data in output columns C–J
function rowHasAnyOutputData(row) {
const values = sheet.getRange(row, 3, 1, 8).getValues()[0]; // C..J
return values.some(v => v !== "" && v !== null);
}
// Write error safely: only if the row has NO output data yet
function safeWriteError(row, message) {
if (rowHasAnyOutputData(row)) {
Logger.log(`Row ${row}: ${message} (not written; row already has data)`);
return;
}
sheet.getRange(row, 3).setValue(message); // write error in Title column only when row is empty
}
// Write extracted data only into blank cells (never overwrite existing)
function safeWriteIfBlank(row, col, value) {
if (value === undefined || value === null) return;
const cell = sheet.getRange(row, col);
const existing = cell.getValue();
if (existing === "" || existing === null) {
cell.setValue(value);
}
}
for (let i = 2; i <= lastRow; i++) {
let url = (sheet.getRange(i, 2).getValue() || "").toString().trim();
if (!url) continue;
// Skip if all output columns C–J already have values
const rowOut = sheet.getRange(i, 3, 1, 8).getDisplayValues()[0]; // C..J
const allFilled = rowOut.every(v => (v || "").toString().trim() !== "");
if (allFilled) continue;
// Validate allowed domains
const isAllowed = allowedPrefixes.some(prefix => url.startsWith(prefix));
if (!isAllowed) {
safeWriteError(i, "Invalid URL");
continue;
}
// Append view_adult=true
if (!url.includes("view_adult=true")) {
url += (url.includes("?") ? "&" : "?") + "view_adult=true";
}
try {
Utilities.sleep(5000);
const response = UrlFetchApp.fetch(url, {
muteHttpExceptions: true,
headers: { "User-Agent": "Mozilla/5.0" }
});
const responseCode = response.getResponseCode();
// For errors other than 525, stop processing (but do not overwrite existing row data)
if (responseCode !== 200 && responseCode !== 525) {
safeWriteError(i, `Error: ${responseCode}`);
continue;
}
const html = response.getContentText();
const data = {
title: "N/A",
author: "N/A",
relationshipTag: "N/A",
wordCount: "N/A",
additionalTags: "N/A",
fandoms: "N/A",
rating: "N/A",
categories: "N/A"
};
let match, matches;
// --- New format extraction ---
match = html.match(/<h4[^>]*class=["']heading["'][^>]*>\\s*<a[^>]*>(.*?)<\\/a>/);
data.title = match ? decodeHtml(match[1]) : data.title;
match = html.match(/<a[^>]*rel=["']author["'][^>]*>(.*?)<\\/a>/);
data.author = match ? decodeHtml(match[1]) : data.author;
match = html.match(/<li[^>]*class=["']relationships["'][^>]*>[\\s\\S]*?<a[^>]*>(.*?)<\\/a>/);
data.relationshipTag = match ? decodeHtml(match[1]) : data.relationshipTag;
match = html.match(/<dd[^>]*class=["']words["'][^>]*>([\\d,]+)<\\/dd>/);
data.wordCount = match ? match[1].trim() : data.wordCount;
matches = [...html.matchAll(/<li[^>]*class=["']freeforms["'][^>]*>[\\s\\S]*?<a[^>]*>(.*?)<\\/a>/g)];
data.additionalTags = matches.length ? matches.map(m => decodeHtml(m[1])).join(", ") : data.additionalTags;
match = html.match(/<h5[^>]*class=["']fandoms\\s+heading["'][^>]*>([\\s\\S]*?)<\\/h5>/);
if (match) {
matches = [...match[1].matchAll(/<a[^>]*>(.*?)<\\/a>/g)];
data.fandoms = matches.length ? matches.map(m => decodeHtml(m[1])).join(", ") : data.fandoms;
}
// --- Rating extraction (two patterns) ---
match = html.match(/<li[^>]*class=["'][^"']*\\brating\\b[^"']*["'][^>]*>[\\s\\S]*?<a[^>]*>(.*?)<\\/a>/i);
if (match) data.rating = decodeHtml(match[1]);
if (data.rating === "N/A") {
match = html.match(/<dd[^>]*class=["'][^"']*\\brating\\b[^"']*tags[^"']*["'][^>]*>[\\s\\S]*?<a[^>]*>(.*?)<\\/a>/i);
if (match) data.rating = decodeHtml(match[1]);
}
// --- Categories extraction (two patterns) ---
matches = [...html.matchAll(/<li[^>]*class=["'][^"']*\\bcategory\\b[^"']*["'][^>]*>[\\s\\S]*?<a[^>]*>(.*?)<\\/a>/gi)];
if (matches.length) {
data.categories = matches.map(m => decodeHtml(m[1])).join(", ");
}
if (data.categories === "N/A") {
match = html.match(/<dd[^>]*class=["'][^"']*\\bcategory\\b[^"']*tags[^"']*["'][^>]*>([\\s\\S]*?)<\\/dd>/i);
if (match) {
const innerLinks = [...match[1].matchAll(/<a[^>]*>(.*?)<\\/a>/gi)];
if (innerLinks.length) data.categories = innerLinks.map(m => decodeHtml(m[1])).join(", ");
}
}
// --- Fallback (old format) ---
if (data.title === "N/A") {
match = html.match(/<h2[^>]*class=["']title\\s+heading["'][^>]*>([\\s\\S]*?)<\\/h2>/);
if (match) data.title = decodeHtml(match[1].replace(/<[^>]+>/g, ""));
}
if (data.relationshipTag === "N/A") {
match = html.match(/<dd[^>]*class=["']relationship\\s+tags["'][^>]*>([\\s\\S]*?)<\\/dd>/);
if (match) {
const inner = match[1].match(/<a[^>]*>(.*?)<\\/a>/);
data.relationshipTag = inner ? decodeHtml(inner[1]) : data.relationshipTag;
}
}
if (data.additionalTags === "N/A") {
match = html.match(/<dd[^>]*class=["']freeform\\s+tags["'][^>]*>([\\s\\S]*?)<\\/dd>/);
if (match) {
matches = [...match[1].matchAll(/<a[^>]*>(.*?)<\\/a>/g)];
data.additionalTags = matches.length ? matches.map(m => decodeHtml(m[1])).join(", ") : data.additionalTags;
}
}
if (data.fandoms === "N/A") {
match = html.match(/<dd[^>]*class=["']fandom\\s+tags["'][^>]*>([\\s\\S]*?)<\\/dd>/);
if (match) {
matches = [...match[1].matchAll(/<a[^>]*>(.*?)<\\/a>/g)];
data.fandoms = matches.length ? matches.map(m => decodeHtml(m[1])).join(", ") : data.fandoms;
}
}
// If 525 and we extracted nothing useful, do NOT overwrite existing row data
const extractedAnything =
data.title !== "N/A" ||
data.author !== "N/A" ||
data.relationshipTag !== "N/A" ||
data.wordCount !== "N/A" ||
data.additionalTags !== "N/A" ||
data.fandoms !== "N/A" ||
data.rating !== "N/A" ||
data.categories !== "N/A";
if (responseCode === 525 && !extractedAnything) {
safeWriteError(i, "Error: 525");
continue;
}
// Write extracted data back to the sheet — only fill blanks
safeWriteIfBlank(i, 3, data.title); // C
safeWriteIfBlank(i, 4, data.author); // D
safeWriteIfBlank(i, 5, data.relationshipTag); // E
safeWriteIfBlank(i, 6, data.wordCount); // F
safeWriteIfBlank(i, 7, data.additionalTags); // G
safeWriteIfBlank(i, 8, data.fandoms); // H
safeWriteIfBlank(i, 9, data.rating); // I
safeWriteIfBlank(i, 10, data.categories); // J
} catch (error) {
// Do not overwrite row data with an error message
safeWriteError(i, "Error fetching data");
Logger.log(`Row ${i}: ${error}`);
}
}
}