Read a whole site into spreadsheet rows

Give it one web address and run it. Its sitemap is read to find the pages, or, with no usable sitemap, the front page is read once and its links taken instead. Each page found is read as clean text, an AI step pulls out the details you asked for, and one row per page goes into your Google Sheet.

Each appended row holds the page address, the page title, then your details in the order you listed, with 'n/a' wherever the page does not say. Links on those pages are never followed, and every address must sit under the site you named, so a link to another site or a look-alike domain is dropped. One run reads a bounded number of pages, and any address already in the sheet's first column is skipped, so a second run picks up where the last one stopped. A page that will not load is named in the run log and the rest carry on.

Before the first run you need a sheet headed 'Page address', 'Page title' and then your fields in the same order, since the columns cannot be named for you.

Set siteUrl to blank.Set siteUrl to blank.

Set fieldsToExtract to blank.Set fieldsToExtract to blank.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set sheetName to Sheet1.Set sheetName to Sheet1.

Set sitemapUrl to blank.Set sitemapUrl to blank.

Set maxPages to 20.Set maxPages to 20.

Set pageCeiling to 50.Set pageCeiling to 50.

Set maxPageCharacters to 12000.Set maxPageCharacters to 12000.

Set columnSeparator to |||.Set columnSeparator to |||.

Get the first item of siteUrl split by :// and save it as siteScheme.Get the first item of siteUrl split by :// and save it as siteScheme.

Get the last item of siteUrl split by :// and save it as siteHostAndPath.Get the last item of siteUrl split by :// and save it as siteHostAndPath.

Get the first item of siteHostAndPath split by / and save it as siteHost.Get the first item of siteHostAndPath split by / and save it as siteHost.

If siteHost is emptyIf siteHost is empty

Yes

Stop and report a problem: Could not read a website name out of the 'siteUrl' step. Paste the site's front page address, for example 'https://acme.test', and run it again.Stop and report a problem: Could not read a website name out of the 'siteUrl' step. Paste the site's front page address, for example 'https://acme.test', and run it again.

No

No steps

Set siteRoot to siteScheme://siteHost/.Set siteRoot to siteScheme://siteHost/.

If sitemapUrl is emptyIf sitemapUrl is empty

Yes

Change sitemapUrl to siteRootsitemap.xml.Change sitemapUrl to siteRootsitemap.xml.

No

No steps

Round maxPages multiplied by 1 to the nearest integer and save it as pageLimit.Round maxPages multiplied by 1 to the nearest integer and save it as pageLimit.

If pageLimit is less than 1If pageLimit is less than 1

Yes

Stop and report a problem: Set 'maxPages' to at least 1 - it is how many pages one run is allowed to read.Stop and report a problem: Set 'maxPages' to at least 1 - it is how many pages one run is allowed to read.

No

No steps

If pageLimit is greater than pageCeilingIf pageLimit is greater than pageCeiling

Yes

Change pageLimit to pageCeiling.Change pageLimit to pageCeiling.

Show in the output: One run never reads more than pageCeiling as text pages, so 'maxPages' was treated as pageCeiling as text. Run it again to pick up where it left off.Show in the output: One run never reads more than pageCeiling as text pages, so 'maxPages' was treated as pageCeiling as text. Run it again to pick up where it left off.

No

No steps

Round maxPageCharacters multiplied by 1 to the nearest integer and save it as pageCharacterLimit.Round maxPageCharacters multiplied by 1 to the nearest integer and save it as pageCharacterLimit.

Set discovered to an empty list.Set discovered to an empty list.

If a step below fails, save the error as sitemapErrorIf a step below fails, save the error as sitemapError

Try

Fetch sitemapUrl and save it as sitemapResponse.Fetch sitemapUrl and save it as sitemapResponse.

If sitemapResponse.ok is yesIf sitemapResponse.ok is yes

Yes

Trim sitemapResponse.body and save it as sitemapText.Trim sitemapResponse.body and save it as sitemapText.

If sitemapText in lowercase doesn't contain <sitemapindexIf sitemapText in lowercase doesn't contain <sitemapindex

Yes

Extract all web addresses from sitemapText and save it as discovered.Extract all web addresses from sitemapText and save it as discovered.

Show in the output: Read the sitemap and found the length of discovered as text address(es) listed in it.Show in the output: Read the sitemap and found the length of discovered as text address(es) listed in it.

No

Show in the output: The sitemap is an index of other sitemaps rather than a list of pages, so the links on the front page are used instead..Show in the output: The sitemap is an index of other sitemaps rather than a list of pages, so the links on the front page are used instead..

No

No steps

On error

Show in the output: Could not read the sitemap: sitemapError.message with sitemapUrl replaced by the sitemap address.Show in the output: Could not read the sitemap: sitemapError.message with sitemapUrl replaced by the sitemap address.

If discovered is emptyIf discovered is empty

Yes

If a step below fails, save the error as frontPageErrorIf a step below fails, save the error as frontPageError

Try

Jina AIRead the page at siteUrl with Jina. Save it as frontPage.Read the page at siteUrl with Jina. Save it as frontPage.

For each frontPageLink in frontPage.links:For each frontPageLink in frontPage.links:

Add frontPageLink.url to the end of discovered and save it as discovered. Add frontPageLink.url to the end of discovered and save it as discovered.

Show in the output: Found the length of discovered as text link(s) on the front page.Show in the output: Found the length of discovered as text link(s) on the front page.

On error

Show in the output: Could not read the front page to find its links: frontPageError.message with siteUrl replaced by the site address.Show in the output: Could not read the front page to find its links: frontPageError.message with siteUrl replaced by the site address.

No

No steps

If discovered is emptyIf discovered is empty

Yes

Stop and report a problem: Found no pages to read. Check the address in the 'siteUrl' step, or point the 'sitemapUrl' step at the site's sitemap.Stop and report a problem: Found no pages to read. Check the address in the 'siteUrl' step, or point the 'sitemapUrl' step at the site's sitemap.

No

No steps

Set alreadyInTheSheet to an empty list.Set alreadyInTheSheet to an empty list.

If a step below fails, save the error as sheetErrorIf a step below fails, save the error as sheetError

Try

Google SheetsRead column 1 of sheetName in spreadsheetUrl. Save it as recordedPages.Read column 1 of sheetName in spreadsheetUrl. Save it as recordedPages.

Combine [] and recordedPages.values into one list and save it as alreadyInTheSheet.Combine [] and recordedPages.values into one list and save it as alreadyInTheSheet.

On error

Show in the output: Could not read the page addresses already in the sheet, so every page found will be read this run..Show in the output: Could not read the page addresses already in the sheet, so every page found will be read this run..

Set candidatePages to an empty list.Set candidatePages to an empty list.

Combine a list of [siteRoot] and discovered into one list and save it as everythingFound.Combine a list of [siteRoot] and discovered into one list and save it as everythingFound.

For each foundLink in everythingFound with duplicates removed:For each foundLink in everythingFound with duplicates removed:

If a step below fails, save the error as linkErrorIf a step below fails, save the error as linkError

Try

Trim foundLink and save it as trimmedLink.Trim foundLink and save it as trimmedLink.

Get the first item of trimmedLink split by # and save it as pageAddress.Get the first item of trimmedLink split by # and save it as pageAddress.

If pageAddress/ doesn't start with siteRoot (ignoring case)If pageAddress/ doesn't start with siteRoot (ignoring case)

Yes

Skip to the next item.Skip to the next item.

No

No steps

If pageAddress ends with .xml (ignoring case) is yesIf pageAddress ends with .xml (ignoring case) is yes

Yes

Skip to the next item.Skip to the next item.

No

No steps

If alreadyInTheSheet contains pageAddress is yesIf alreadyInTheSheet contains pageAddress is yes

Yes

Skip to the next item.Skip to the next item.

No

No steps

If candidatePages contains pageAddress is yesIf candidatePages contains pageAddress is yes

Yes

Skip to the next item.Skip to the next item.

No

No steps

Add pageAddress to the end of candidatePages and save it as candidatePages.Add pageAddress to the end of candidatePages and save it as candidatePages.

On error

Show in the output: Skipped one discovered link because it had no address..Show in the output: Skipped one discovered link because it had no address..

If candidatePages is emptyIf candidatePages is empty

Yes

Finish the run: Nothing new to read - every page found is either already in the sheet or sits on a different site.Finish the run: Nothing new to read - every page found is either already in the sheet or sits on a different site.

No

No steps

Take the first pageLimit items of candidatePages and save it as pagesToRead.Take the first pageLimit items of candidatePages and save it as pagesToRead.

Show in the output: Found the length of candidatePages as text page(s) on this site that are not in the sheet yet; reading the length of pagesToRead as text of them this run.Show in the output: Found the length of candidatePages as text page(s) on this site that are not in the sheet yet; reading the length of pagesToRead as text of them this run.

Set rowsWritten to 0.Set rowsWritten to 0.

Set pagesWithoutRows to an empty list.Set pagesWithoutRows to an empty list.

For each pageAddress in pagesToRead:For each pageAddress in pagesToRead:

If a step below fails, save the error as pageErrorIf a step below fails, save the error as pageError

Try

Jina AIRead the page at pageAddress with Jina. Save it as page.Read the page at pageAddress with Jina. Save it as page.

Trim page.content and save it as pageText.Trim page.content and save it as pageText.

Set pageTitle to blank.Set pageTitle to blank.

If a step below fails, save the error as titleErrorIf a step below fails, save the error as titleError

Try

Trim page.title and save it as pageTitle.Trim page.title and save it as pageTitle.

On error

Change pageTitle to blank.Change pageTitle to blank.

If pageText isn't emptyIf pageText isn't empty

Yes

Set promptTask to You are reading one page of a website and filling in one row of a spreadsheet.Set promptTask to You are reading one page of a website and filling in one row of a spreadsheet. Pull out these details, in this exact order: fieldsToExtract.

Set promptRules to Set promptRules to Reply with ONE line and nothing else. Separate the values with columnSeparator in the order listed above. Do not repeat the field names, do not add headings, bullets, numbering or any commentary, and do not wrap values in quotes. Write n/a for anything the page does not say.

Set promptPage to Set promptPage to Page address: pageAddress Page title: pageTitle Page content: pageText truncated to pageCharacterLimit characters.

Set prompt to promptTask plus promptRules plus promptPage.Set prompt to promptTask plus promptRules plus promptPage.

OpenAIAsk OpenAI: prompt. Save it as extraction.Ask OpenAI: prompt. Save it as extraction.

Trim extraction.response_text and save it as answerLine.Trim extraction.response_text and save it as answerLine.

Create a list of [pageAddress, pageTitle] and save it as row.Create a list of [pageAddress, pageTitle] and save it as row.

For each extractedValue in answerLine split by columnSeparator:For each extractedValue in answerLine split by columnSeparator:

Add extractedValue to the end of row and save it as row. Add extractedValue to the end of row and save it as row.

Google SheetsAdd a row to sheetName in spreadsheetUrl.Add a row to sheetName in spreadsheetUrl.

Add 1 to rowsWritten.Add 1 to rowsWritten.

No

Add pageAddress to the end of pagesWithoutRows and save it as pagesWithoutRows.Add pageAddress to the end of pagesWithoutRows and save it as pagesWithoutRows.

Show in the output: Read '@pageAddress' but it had no readable text, so no row was written for it.Show in the output: Read '@pageAddress' but it had no readable text, so no row was written for it.

On error

Add pageAddress to the end of pagesWithoutRows and save it as pagesWithoutRows.Add pageAddress to the end of pagesWithoutRows and save it as pagesWithoutRows.

Show in the output: Could not read '@pageAddress': pageError.message.Show in the output: Could not read '@pageAddress': pageError.message.

Show in the output: Finished. Wrote rowsWritten as text row(s) to '@sheetName'.Show in the output: Finished. Wrote rowsWritten as text row(s) to '@sheetName'.

If pagesWithoutRows isn't emptyIf pagesWithoutRows isn't empty

Yes

Show in the output: No row was written for: pagesWithoutRows joined by , .Show in the output: No row was written for: pagesWithoutRows joined by , .

No

No steps