The avatar for @simonw
Simon Willison
Public
Edited
Feb 17
7 stars
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
newsletterHTML = {
let html = "";
const idOrder = storyOrder.map((s) => parseInt(s.split(":")[0], 10));
if (entries.length) {
// Need them in storyOrder
html += `<p>In this newsletter:</p><ul>${entries
.sort((a, b) => {
const indexA = idOrder.indexOf(a.id);
const indexB = idOrder.indexOf(b.id);
return indexA - indexB;
})
.map((e) => `<li>${e.title}</li>`)
.join("\n")}</ul></p>`;
}
let extras = [];
if (blogmarks.length) {
extras.push(`${blogmarks.length} link${blogmarks.length > 1 ? "s" : ""}`);
}
if (quotations.length) {
extras.push(
`${quotations.length} quotation${quotations.length > 1 ? "s" : ""}`
);
}
if (tils.length) {
extras.push(`${tils.length} TIL${tils.length > 1 ? "s" : ""}`);
}
if (extras) {
html += `<p>Plus ${extras.join(" and ")}</p>`;
}
html += content
.sort((a, b) => {
const notFoundIndex = idOrder.length + 1;

const indexA =
idOrder.indexOf(a.id) !== -1 ? idOrder.indexOf(a.id) : notFoundIndex;
const indexB =
idOrder.indexOf(b.id) !== -1 ? idOrder.indexOf(b.id) : notFoundIndex;

return indexA - indexB;
})
.map((c) => c.html + "<hr>")
.join("\n");
return html;
}
Insert cell
html_no_entries = content
.filter((e) => e.type != "entry")
.map((e) => e.html)
.join("\n\n")
Insert cell
Insert cell
raw_content = {
return await (
await fetch(
`https://datasette.simonwillison.net/simonwillisonblog.json?sql=${encodeURIComponent(
sql
)}&_shape=array&numdays=${numDays}`
)
).json();
}
Insert cell
sql = `
with content as (
select
id,
'entry' as type,
title,
created,
slug,
'<h3><a href="' || 'https://simonwillison.net/' || strftime('%Y/', created)
|| substr('JanFebMarAprMayJunJulAugSepOctNovDec', (strftime('%m', created) - 1) * 3 + 1, 3)
|| '/' || cast(strftime('%d', created) as integer) || '/' || slug || '/' || '">'
|| title || '</a> - ' || date(created) || '</h3>' || body
as html,
'null' as json,
'' as external_url
from blog_entry
union all
select
id,
'blogmark' as type,
link_title,
created,
slug,
'<p><strong>Link</strong> ' || date(created) || ' <a href="'|| link_url || '">'
|| link_title || '</a>:</p><p>' || ' ' || replace(commentary, '\n', '<br>') || '</p>'
as html,
json_object(
'created', date(created), 'link_url', link_url, 'link_title', link_title, 'commentary', commentary,
'use_markdown', use_markdown
) as json,
link_url as external_url
from blog_blogmark
union all
select
id,
'quotation' as type,
source,
created,
slug,
'<strong>Quote</strong> ' || date(created) || '<blockquote><p><em>'
|| replace(quotation, '
', '<br>') || '</em></p></blockquote><p><a href="' ||
coalesce(source_url, '#') || '">' || source || '</a></p>'
as html,
'null' as json,
source_url as external_url
from blog_quotation
union all
select
rowid,
'til' as type,
title,
created,
'null' as slug,
'<p><strong>TIL</strong> ' || date(created) || ' <a href="'|| 'https://til.simonwillison.net/' || topic || '/' || slug || '">' || title || '</a>:' || ' ' || substr(html, 1, instr(html, '</p>') - 1) || ' &#8230;</p>' as html,
'null' as json,
'https://til.simonwillison.net/' || topic || '/' || slug as external_url
from til
),
collected as (
select
id,
type,
title,
case
when type = 'til'
then external_url
else 'https://simonwillison.net/' || strftime('%Y/', created)
|| substr('JanFebMarAprMayJunJulAugSepOctNovDec', (strftime('%m', created) - 1) * 3 + 1, 3) ||
'/' || cast(strftime('%d', created) as integer) || '/' || slug || '/'
end as url,
created,
html,
json,
external_url,
case
when type = 'entry' then (
select json_group_array(tag)
from blog_tag
join blog_entry_tags on blog_tag.id = blog_entry_tags.tag_id
where blog_entry_tags.entry_id = content.id
)
when type = 'blogmark' then (
select json_group_array(tag)
from blog_tag
join blog_blogmark_tags on blog_tag.id = blog_blogmark_tags.tag_id
where blog_blogmark_tags.blogmark_id = content.id
)
when type = 'quotation' then (
select json_group_array(tag)
from blog_tag
join blog_quotation_tags on blog_tag.id = blog_quotation_tags.tag_id
where blog_quotation_tags.quotation_id = content.id
)
else '[]'
end as tags
from content
where created >= date('now', '-' || :numdays || ' days')
order by created desc
)
select id, type, title, url, created, html, json, external_url, tags
from collected
order by
case type
when 'entry' then 0
else 1
end,
case type
when 'entry' then created
else -strftime('%s', created)
end desc;
`
Insert cell
Insert cell
content_filtered = (skipExisting
? raw_content.filter(
(e) =>
!previousLinks.includes(e.url) &&
!previousLinks.includes(e.external_url)
)
: raw_content
).map(({ tags, ...rest }) => ({ ...rest, tags: JSON.parse(tags) }))
Insert cell
raw_content
Type Table, then Shift-Enter. Ctrl-space for more options.

Insert cell
content_filtered
Insert cell
content = onlyPreCutoff
? content_filtered.map((e) => {
if (
e.json != "null" &&
e.type == "blogmark" &&
JSON.parse(e.json).use_markdown
) {
// Render the markdown instead of using the HTML
let entry = JSON.parse(JSON.stringify(e));
let info = JSON.parse(e.json);
let commentary = md`${info.commentary}`.innerHTML;
entry.html = `<p><strong>Link</strong> ${info.created} <a href="${info.link_url}">
${info.link_title}</a>: </p>${commentary}`;
return entry;
}
// Apply Markdown to quotations
if (e.type == "quotation") {
e.html = md`${e.html}`.innerHTML;
}
if (e.html) {
// Create a copy
let entry = JSON.parse(JSON.stringify(e));
entry.html = entry.html.split("<!-- cutoff -->")[0];
return entry;
}
})
: content_filtered
Insert cell
Insert cell
Inputs.table(content)
Insert cell
Insert cell
Insert cell
entries[0].html
Insert cell
entries = content.filter((e) => e.type == "entry")
Insert cell
blogmarks = content.filter((e) => e.type == "blogmark")
Insert cell
quotations = content.filter((e) => e.type == "quotation")
Insert cell
tils = content.filter((e) => e.type == "til")
Insert cell
Insert cell
Insert cell
Inputs.table(previous_links_search)
Insert cell
Insert cell
Insert cell
Insert cell
previousNewsletterHtml = {
const result = rssDoc.evaluate(
"//content:encoded",
rssDoc,
namespaceResolver,
XPathResult.ANY_TYPE,
null
);
let node;
let text = [];
while ((node = result.iterateNext())) {
text.push(node.textContent);
}
return text.join("\n");
}
Insert cell
daysSinceLastNewsletter = {
const date = new Date(previousNewsletterDate);
return parseFloat(((new Date() - date) / (1000 * 60 * 60 * 24)).toFixed(1));
}
Insert cell
rssDoc
Insert cell
previousNewsletterDate = rssDoc
.evaluate(
"//channel/item/pubDate",
rssDoc,
namespaceResolver,
XPathResult.ANY_TYPE,
null
)
.iterateNext().textContent
Insert cell
rssBody = {
if (rssInput) {
return rssInput;
} else {
const response = await fetch(
"https://restless-cherry-7938.simonw.workers.dev/"
);
return await response.text();
}
}
Insert cell
rssDoc = {
const response = await fetch(
"https://raw.githubusercontent.com/simonw/simonwillisonblog-backup/main/simonw-substack-com.xml"
);
const parser = new DOMParser();
return parser.parseFromString(rssBody, "application/xml");
}
Insert cell
rssBody
Insert cell
namespaceResolver = (prefix) => {
const ns = {
content: "http://purl.org/rss/1.0/modules/content/"
};
return ns[prefix] || null;
}
Insert cell
// Adapted from https://observablehq.com/@jashkenas/select-order-input - but I removed the checkboxes
function selectOrder(items, options) {
let dragging = null;

const list = html`<ul style="display: inline-block; min-width: 200px; margin: 5px 0; padding: 0; font-size: 0.85em;">${items.map(
(i) => html`
<li draggable="true" style="display: block; cursor: move; padding: 3px 5px 3px 0; border-top: ${topBorder}; border-bottom: ${bottomBorder}">${i}</li>
`
)}</ul>`;

function getValue() {
return Array.from(list.childNodes).map((li) => li.innerText);
}

function ondragstart(event) {
var target = getLi(event.target);
dragging = target;
event.dataTransfer.setData("text/plain", null);
}

function ondragover(event) {
event.preventDefault();
var target = getLi(event.target);
var bounding = target.getBoundingClientRect();
var offset = bounding.y + bounding.height / 2;
if (event.clientY - offset > 0) {
target.style["border-bottom"] = dragBorder;
target.style["border-top"] = topBorder;
target.inserting = "below";
} else {
target.style["border-top"] = dragBorder;
target.style["border-bottom"] = bottomBorder;
target.inserting = "above";
}
}

function ondragleave(event) {
reset(getLi(event.target));
}

function ondrop(event) {
event.preventDefault();
var target = getLi(event.target);
if (target.inserting === "below") {
reset(target);
target.parentNode.insertBefore(dragging, event.target.nextSibling);
} else {
reset(target);
target.parentNode.insertBefore(dragging, event.target);
}
list.value = getValue();
list.dispatchEvent(new CustomEvent("input"));
}

function oninput(event) {
list.value = getValue();
}

return Object.assign(list, {
ondragstart,
ondragover,
ondragleave,
ondrop,
oninput,
value: getValue()
});
}
Insert cell
function reset(li) {
li.style["border-top"] = topBorder;
li.style["border-bottom"] = bottomBorder;
}
Insert cell
topBorder = "solid 1px transparent"
Insert cell
bottomBorder = "solid 1px #ddd"
Insert cell
dragBorder = "solid 1px blue"
Insert cell
function getLi(target) {
while (
target.nodeName.toLowerCase() != "li" &&
target.nodeName.toLowerCase() != "body"
) {
target = target.parentNode;
}
if (target.nodeName.toLowerCase() == "body") {
return false;
} else {
return target;
}
}
Insert cell
"b" + md`# hello\n\nthere`.innerHTML
Insert cell

One platform to build and deploy the best data apps

Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.
Learn more