Technical Sitemap Builder

Upload a Screaming Frog crawl export and (optionally) a GSC export to generate a structured technical sitemap CSV matching the standard planning template.

🐸

Screaming Frog Crawl

Required

In Screaming Frog: crawl your site → Internal tab → Export CSV. The tool filters to HTML pages automatically.

📈

Ahrefs Keyword Volume CSV

Optional

CSV with Keyword and Volume columns (e.g. from Ahrefs, Semrush, or similar). If a page keyword matches an entry here, the volume is appended in brackets — e.g. King size beds (5,500).

🔑

GSC Query Data — Search Analytics for Sheets

Optional

CSV with Query, Page, Clicks columns. Export from GSC → Pages → click a page → Queries, or use a tool like Search Analytics for Sheets. Pre-populates Target Keywords with the top 5 queries per page.

📊

GSC Page Data — Last 12 Months

Optional

Upload the GSC zip (must contain Pages.csv) or a Pages CSV directly. Populates Clicks and Impressions columns.

Google Sheets Formatting Script

Import the CSV into Google Sheets, then paste this Apps Script to auto-format the sitemap with colour-coded rows, dropdown pills, column widths, and conditional formatting. Go to Extensions → Apps Script, paste the code, save, then refresh your sheet and use the SEO Tools menu.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('SEO Tools')
    .addItem('Format Technical Sitemap', 'formatTechnicalSitemap')
    .addToUi();
}

function formatTechnicalSitemap() {
  const ss      = SpreadsheetApp.getActiveSpreadsheet();
  const sheet   = ss.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();

  if (lastRow < 2) { SpreadsheetApp.getUi().alert('No data found.'); return; }

  const rawHeaders = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  const headers    = rawHeaders.map(h => h.toString().split('\n')[0].toLowerCase().trim());

  function col(needle) {
    const i = headers.findIndex(h => h.includes(needle.toLowerCase()));
    return i >= 0 ? i + 1 : -1;
  }

  const C = {
    id:          1,
    pageName:    col('page name'),
    level:       col('level'),
    existing:    col('existing page'),
    clicks:      col('clicks'),
    impressions: col('impressions'),
    template:    col('content template'),
    action:      col('action'),
    currentUrl:  col('current site url'),
    inNav:       col('should link'),
    design:      col('design template'),
    stq:         col('stq'),
    client:      col('client comments'),
    keywords:    col('target keywords'),
    urlL1:       col('url level 1'),
    urlL5:       col('url level 5'),
    newUrl:      col('new suggested'),
    brief:       col('content brief'),
    owner:       col('owner'),
    approved:    col('content approved'),
    commenced:   col('content loading commenced'),
    loaded:      col('content loaded'),
    metaOpt:     col('meta content'),
    metaTitle:   col('meta title'),
    titleLen:    col('meta title len'),
    metaDesc:    col('meta desc'),
    descLen:     col('meta desc len'),
    h1:          col('h1'),
    copyDoc:     col('copy doc'),
    designed:    col('designed'),
  };

  // 1. Header row
  sheet.getRange(1, 1, 1, lastCol)
    .setBackground('#000000').setFontColor('#ffffff')
    .setFontWeight('bold').setFontSize(9)
    .setFontFamily('Outfit').setVerticalAlignment('middle').setWrap(true);
  sheet.setRowHeight(1, 52);
  sheet.setFrozenRows(1);
  if (C.pageName > 0) sheet.setFrozenColumns(C.pageName);

  // 2. Body rows — plain white
  const dataRows = lastRow - 1;
  sheet.getRange(2, 1, dataRows, lastCol)
    .setBackground('#ffffff').setFontColor('#1a1a2e')
    .setFontWeight('normal').setFontSize(9)
    .setFontFamily('Outfit').setVerticalAlignment('middle');

  // 3. ID column
  sheet.getRange(2, C.id, dataRows, 1)
    .setBackground('#f3f4f6').setFontColor('#9ca3af').setHorizontalAlignment('center');

  // 4. Colour PAGE NAME column by depth
  if (C.pageName > 0) {
    const allData = sheet.getRange(2, 1, dataRows, lastCol).getValues();
    const DEPTH = {
      root:   { bg: '#0d1b5e', fg: '#ffffff', fw: 'bold'   },
      cat0:   { bg: '#1a3c6e', fg: '#ffffff', fw: 'bold'   },
      depth1: { bg: '#1e5fa0', fg: '#ffffff', fw: 'normal' },
      deep:   { bg: '#ffffff', fg: '#1a1a2e', fw: 'normal' },
      empty:  { bg: '#f3f4f6', fg: '#9ca3af', fw: 'normal' },
    };
    const bgCol = [], fgCol = [], fwCol = [];
    for (let i = 0; i < dataRows; i++) {
      const name  = (allData[i][C.pageName - 1] ?? '').toString().trim();
      const level = (allData[i][C.level - 1]    ?? '').toString().trim();
      let c;
      if (!name)                      c = DEPTH.empty;
      else if (level === 'Root')      c = DEPTH.root;
      else if (!name.startsWith('-')) c = DEPTH.cat0;
      else if (/^-\s*-/.test(name))   c = DEPTH.deep;
      else                            c = DEPTH.depth1;
      bgCol.push([c.bg]); fgCol.push([c.fg]); fwCol.push([c.fw]);
    }
    sheet.getRange(2, C.pageName, dataRows, 1)
      .setBackgrounds(bgCol).setFontColors(fgCol).setFontWeights(fwCol).setWrap(true);
  }

  // 5. Data validation
  const nRows = Math.max(dataRows, 500);
  function dv(list, strict) {
    return SpreadsheetApp.newDataValidation()
      .requireValueInList(list, true).setAllowInvalid(!strict).build();
  }
  if (C.level    > 0) sheet.getRange(2, C.level,    nRows, 1).setDataValidation(dv(['Root','Cat','Individual'], true));
  if (C.existing > 0) sheet.getRange(2, C.existing, nRows, 1).setDataValidation(dv(['Yes','No'], true));
  if (C.action   > 0) sheet.getRange(2, C.action,   nRows, 1).setDataValidation(dv(['TBC','New Page','Revise Page','Leave as is','Redirect','Remove'], false));
  if (C.inNav    > 0) sheet.getRange(2, C.inNav,    nRows, 1).setDataValidation(dv(['TBC','Yes','No'], false));
  const cbRule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  [C.approved, C.commenced, C.loaded].forEach(c => {
    if (c > 0) sheet.getRange(2, c, nRows, 1).setDataValidation(cbRule);
  });

  // 6. Conditional formatting — pastel pills
  sheet.clearConditionalFormatRules();
  const maxR = Math.max(lastRow, 500);
  function cf(colNum, value, bg, fg) {
    return SpreadsheetApp.newConditionalFormatRule()
      .whenTextEqualTo(value).setBackground(bg).setFontColor(fg)
      .setRanges([sheet.getRange(2, colNum, maxR, 1)]).build();
  }
  const rules = [];
  if (C.level > 0) {
    rules.push(cf(C.level,'Root','#fef3c7','#92400e'), cf(C.level,'Cat','#ffedd5','#9a3412'), cf(C.level,'Individual','#dcfce7','#166534'));
  }
  if (C.existing > 0) {
    rules.push(cf(C.existing,'Yes','#dcfce7','#166534'), cf(C.existing,'No','#fee2e2','#991b1b'));
  }
  if (C.action > 0) {
    rules.push(cf(C.action,'TBC','#f3f4f6','#6b7280'), cf(C.action,'New Page','#dcfce7','#166534'), cf(C.action,'Revise Page','#fef3c7','#92400e'), cf(C.action,'Leave as is','#dbeafe','#1e40af'), cf(C.action,'Redirect','#ffedd5','#9a3412'), cf(C.action,'Remove','#fee2e2','#991b1b'));
  }
  if (C.inNav > 0) {
    rules.push(cf(C.inNav,'TBC','#f3f4f6','#6b7280'), cf(C.inNav,'Yes','#dcfce7','#166534'), cf(C.inNav,'No','#fee2e2','#991b1b'));
  }
  sheet.setConditionalFormatRules(rules);

  // 7. Column widths
  const widths = {
    [C.id]:45,[C.pageName]:300,[C.level]:100,[C.existing]:110,[C.clicks]:75,[C.impressions]:100,
    [C.template]:130,[C.action]:120,[C.currentUrl]:420,[C.inNav]:130,[C.design]:130,[C.stq]:140,
    [C.client]:140,[C.keywords]:200,[C.newUrl]:200,[C.brief]:140,[C.owner]:90,[C.approved]:110,
    [C.commenced]:130,[C.loaded]:100,[C.metaOpt]:120,[C.metaTitle]:260,[C.titleLen]:70,
    [C.metaDesc]:300,[C.descLen]:70,[C.h1]:260,[C.copyDoc]:90,[C.designed]:90,
  };
  if (C.urlL1 > 0 && C.urlL5 > 0) { for (let c = C.urlL1; c <= C.urlL5; c++) widths[c] = 120; }
  Object.entries(widths).forEach(([c, w]) => { if (+c > 0) sheet.setColumnWidth(+c, w); });

  // 8. Alignment
  [C.id, C.level, C.existing, C.clicks, C.impressions, C.action, C.inNav].forEach(c => {
    if (c > 0) sheet.getRange(2, c, dataRows, 1).setHorizontalAlignment('center');
  });

  // 9. Wrap
  [C.pageName, C.currentUrl, C.keywords, C.metaTitle, C.metaDesc].forEach(c => {
    if (c > 0) sheet.getRange(2, c, dataRows, 1).setWrap(true);
  });

  // 10. Row heights
  for (let r = 2; r <= lastRow; r++) sheet.setRowHeight(r, 28);

  SpreadsheetApp.getUi().alert('✅ Technical sitemap formatted!');
}

Technical Sitemap Builder

How to Use the Technical Sitemap Builder

Google Sheets Formatting Script