Finds the pages on a site from its sitemap or front page, reads each one, and adds a row per page to your sheet.
Uses
Google Sheets
Jina AI
OpenAI
Read a whole site into spreadsheet rows
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 emptyYes
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 emptyYes
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 1Yes
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 pageCeilingYes
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 sitemapErrorTry
Fetch sitemapUrl and save it as sitemapResponse.Fetch sitemapUrl and save it as sitemapResponse.If sitemapResponse.ok is yesIf sitemapResponse.ok is yesYes
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 <sitemapindexYes
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 emptyYes
If a step below fails, save the error as frontPageErrorIf a step below fails, save the error as frontPageErrorTry
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 emptyYes
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 sheetErrorTry
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 linkErrorTry
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 yesYes
Skip to the next item.Skip to the next item.No
No steps
If alreadyInTheSheet contains pageAddress is yesIf alreadyInTheSheet contains pageAddress is yesYes
Skip to the next item.Skip to the next item.No
No steps
If candidatePages contains pageAddress is yesIf candidatePages contains pageAddress is yesYes
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 emptyYes
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 pageErrorTry
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 titleErrorTry
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 emptyYes
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 emptyYes
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