2f:T58d,# Blog to newsletter **TODO before next send: code to replace lite-youtube tags with iframes that work in Substack** I've started a newsletter at [simonw.substack.com](https://simonw.substack.com/) which I send out weekly-ish with content derived from my blog at https://simonwillison.net/ This notebook helps me do that: it generates HTML from the [Datasette backup of my blog](https://datasette.simonwillison.net/) ([trigger](https://github.com/simonw/simonwillisonblog-backup/actions/workflows/backup.yml)) such that I can copy and paste it into Substack. Further relevant reading: - **[Semi-automating a Substack newsletter with an Observable notebook](https://simonwillison.net/2023/Apr/4/substack-observable/)** - [TIL: Copy rich text to the clipboard](https://til.simonwillison.net/javascript/copy-rich-text-to-clipboard) explains how the rich text copying mechanism works. - [sqlite-comprehend: run AWS entity extraction against content in a SQLite database](https://simonwillison.net/2022/Jul/11/sqlite-comprehend/) includes a section explaining how the [simonwillisonblog-backup](https://github.com/simonw/simonwillisonblog-backup) repository I use to publish that Datasette instance works. You can **[skip to the implementation](https://observablehq.com/@simonw/blog-to-newsletter#cell-214)** to see detailed notes on how this works. ${daysSinceLastNewsletter.toFixed(2)} days since last the newsletter.30:T660,newsletterHTML = { let html = ""; const idOrder = storyOrder.map((s) => parseInt(s.split(":")[0], 10)); if (entries.length) { // Need them in storyOrder html += `

In this newsletter:

${e.title}

`; } 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 (notes.length) { extras.push(`${notes.length} note${notes.length > 1 ? "s" : ""}`); } if (extras) { html += `

Plus ${extras.join(" and ")}

`; } html += `

If you find this newsletter useful, please consider sponsoring me via GitHub. $10/month and higher sponsors get a monthly newletter with my summary of the most important trends of the past 30 days.

` 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 + "

") .join("\n"); return html; }31:T101c,sql = ` with content as ( select id, 'entry' as type, title, created, slug, '

' || title || ' - ' || date(created) || '

' || body as html, 'null' as json, '' as external_url from blog_entry union all select id, 'blogmark' as type, link_title, created, slug, '

Link ' || date(created) || ' ' || link_title || ':

' || ' ' || replace(commentary, '\n', '
') || '

' 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, 'quote ' || date(created) || '

' || replace(quotation, ' ', '
') || '

' || source || '' || case when nullif(trim(context), '') is not null then ', ' || context else '' end || '

' as html, 'null' as json, source_url as external_url from blog_quotation union all select id, 'note' as type, case when title is not null and title <> '' then title else 'Note on ' || date(created) end, created, slug, 'No HTML', json_object( 'created', date(created), 'link_url', 'https://simonwillison.net/' || strftime('%Y/', created) || substr('JanFebMarAprMayJunJulAugSepOctNovDec', (strftime('%m', created) - 1) * 3 + 1, 3) || '/' || cast(strftime('%d', created) as integer) || '/' || slug || '/', 'link_title', '', 'commentary', body, 'use_markdown', 1 ), '' as external_url from blog_note union all select rowid, 'til' as type, title, created, 'null' as slug, '

TIL ' || date(created) || ' ' || title || ':' || ' ' || substr(html, 1, instr(html, '

') - 1) || ' …

' 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; `32:T4af,content = onlyPreCutoff ? content_filtered.map((e) => { if ( e.json != "null" && (e.type == "blogmark" || e.type == "note") && 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); if (e.type == "blogmark") { let commentary = md`${info.commentary}`.innerHTML; entry.html = `

Link ${info.created} ${info.link_title}:

${commentary}`; return entry; } else if (e.type == "note") { let commentary = md`${info.commentary}`.innerHTML; entry.html = `

Note ${info.created} ${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("")[0]; return entry; } }) : content_filtered33:T788,// Adapted from https://observablehq.com/@jashkenas/select-order-input - but I removed the checkboxes function selectOrder(items, options) { let dragging = null; const list = html`

${i}

`; 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() }); }34:[] 2d:{"title":"Blog to newsletter","cells":[{"id":0,"value":"$2f","pinned":false,"hidden":false,"mode":"md"},{"id":360,"value":"$${entries.length > 1 ? '### Set order of the stories:' : ''}","pinned":false,"hidden":false,"mode":"md"},{"id":353,"value":"viewof storyOrder = selectOrder(\n entries.map((e) => `${e.id}: ${e.title}`).reverse()\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":22,"value":"Object.assign(\n html`