How to Connect WordPress to Google Sheets

WordPress's built-in export produces an XML file designed for migrating between sites, not a table you can analyse, so getting content into a spreadsheet means the REST API. Two things account for most failed first attempts: the API returns 404 on every route when permalinks are set to Plain, and titles and content arrive as objects rather than strings.

WordPress to Google Sheets for free (Apps Script + REST API)

First, check Settings → Permalinks is not set to Plain. If it is, choose Post name and save, which flushes the rewrite rules that create the /wp-json/ routes. Public posts can be read without any authentication at all, so if you only need published content you can skip credentials entirely.

For drafts, private posts or any custom field, generate an Application Password from Users → Profile → Application Passwords and use it with basic auth. This is built into WordPress and can be revoked without changing your real password.

1function importWordPressPosts() {2  const SITE = "https://yoursite.com";3  const USER = "your-username";4  const APP_PASSWORD = "xxxx xxxx xxxx xxxx xxxx xxxx";  // Application Password5 6  const headers = {7    Authorization: "Basic " + Utilities.base64Encode(USER + ":" + APP_PASSWORD)8  };9 10  const rows = [];11  let page = 1;12 13  while (true) {14    const url = SITE + "/wp-json/wp/v2/posts"15      + "?per_page=100&page=" + page      // 100 is the maximum16      + "&status=any"                     // defaults to publish only17      + "&_embed=author,wp:term";         // inline author and term names18 19    const response = UrlFetchApp.fetch(url, { headers, muteHttpExceptions: true });20 21    // Asking past the last page returns 400 rather than an empty array.22    if (response.getResponseCode() === 400) break;23    if (response.getResponseCode() !== 200) {24      throw new Error(response.getResponseCode() + ": " + response.getContentText());25    }26 27    const posts = JSON.parse(response.getContentText());28    if (!posts.length) break;29 30    posts.forEach((p) => {31      const embedded = p._embedded || {};32      const author = (embedded.author || [{}])[0].name || p.author;33      // wp:term is an array of arrays: categories, then tags.34      const terms = (embedded["wp:term"] || []);35      const categories = (terms[0] || []).map((t) => t.name).join(", ");36      const tags = (terms[1] || []).map((t) => t.name).join(", ");37 38      rows.push([39        p.id,40        stripHtml(p.title.rendered),      // objects, not strings41        p.status,42        author,43        categories,44        tags,45        p.date,46        p.modified,47        stripHtml(p.excerpt.rendered).slice(0, 300),48        p.link,49      ]);50    });51 52    if (posts.length < 100) break;53    page++;54  }55 56  const header = ["ID","Title","Status","Author","Categories","Tags",57                  "Published","Modified","Excerpt","URL"];58  const ss = SpreadsheetApp.getActiveSpreadsheet();59  const sheet = ss.getSheetByName("Posts") || ss.insertSheet("Posts");60  sheet.clearContents();61  sheet.getRange(1, 1, 1, header.length).setValues([header]);62  if (rows.length) {63    sheet.getRange(2, 1, rows.length, header.length).setValues(rows);64  }65}66 67// Rendered values contain HTML tags and entities.68function stripHtml(html) {69  return String(html || "")70    .replace(/<[^>]+>/g, "")71    .replace(/&amp;/g, "&").replace(/&#8217;/g, "'")72    .replace(/&#8220;|&#8221;/g, '"').replace(/&nbsp;/g, " ")73    .trim();74}

Two behaviours worth knowing. Requesting a page beyond the last one returns HTTP 400 rather than an empty array, so a loop that only checks for an empty result will throw. And the _embedded["wp:term"] structure is an array of arrays, ordered by the taxonomies attached to the post type, with categories first and tags second on standard posts.

Limits: Apps Script's six-minute cap, plus each page is a live database query against the site, so large blogs on modest hosting get slow. Custom fields are only exposed when registered with show_in_rest, so ACF fields often need the plugin's REST setting enabling first.

The Brooked method

Brooked unwraps the rendered objects, strips HTML entities, resolves author and term IDs to names, and handles the end-of-pagination behaviour, so posts arrive as clean text columns. Posts, pages, custom post types, users and comments can all import into one spreadsheet on a single schedule.

Step 1: Install Brooked

Install Brooked from the Google Workspace Marketplace. Free tier includes 100 imports per month, no credit card required.

Step 2: Connect WordPress

Choose WordPress in Brooked, enter your site URL, username and an Application Password. Confirm permalinks are not set to Plain first, since that single setting disables every REST route.

Step 3: Schedule the refresh

Set the import to re-run daily or weekly so a content audit stays current, picking up newly published posts and updated modification dates without anyone re-running an export.

Brooked AI, WordPress

Live

Ask anything about your WordPress data…

Method comparison

Common use cases

• Content audits: every post with author, category, publish and modified dates in one table
• Refresh planning: find posts untouched longest and prioritise updates
• Editorial reporting: output per author or category over time
• SEO joins: combine post metadata with Search Console data in the same sheet
• Migration prep: inventory posts, pages and custom post types before a replatform

Troubleshooting common issues

Related Articles

• How to Connect Pipedrive to Google Sheets
• How to Connect Monday.com to Google Sheets
• Connect Google Search Console to Google Sheets

Frequently asked questions