This tutorial explains how you can easily connect WordPress to Google Sheets for free. It allows you to automatically send data from Google Sheets to WordPress or fetch WordPress details into Google Sheets.
Prerequisite : No coding or special tools are required. You just need admin access to your WordPress site.
The first step is to authenticate your API requests. WordPress needs authentication to verify user credentials. You will need your WordPress username and an application password.
- Log in to your WordPress dashboard.
- Go to Users > Profile.
- Scroll down to the Application Passwords section.
- Enter a name for the application. Enter any name you want.
- Click Add New Application Password to generate a password.
- Copy the password and save it for future reference.
- Make a copy by selecting File > Make a copy.
- Click on the Extensions menu in the top menu bar.
- Select Apps Script from the dropdown. This will open the Apps Script editor in a new tab.
- In the script editor, enter your site URL, username and application password in the following code.
- Save project by clicking on Ctrl+S key.
const wpSite = "https://www.example.com";
const wpUsername = "xxxxxxxxxxxx";
const wpPassword = "XXXX XXXX XXXX XXXX"; // Application Password
Note : If Google shows a popup asking for permissions, it means that the script has not been tested on your account before and Google requires your approval. There is nothing to worry about. This is a standard warning message that appears the first time you use a new script.
Click Review Permissions to proceed to the next screen then click Go to Project (unsafe) to grant the script the permissions it needs.
Posts
List WordPress Posts
In Google sheets, enter the following function in any cell. It returns details of all posts from your WordPress site.
=GET_WP_CONTENT("posts")If you want details of a specific post, you can specify id in the second argument.
=GET_WP_CONTENT("posts",357)The above function returns the details of post with ID 357 from your WordPress site.
To find the post ID, you need to open the post in edit mode and then check the browser's address bar. The URL will appear something like post.php?post=357&action=edit. The number after post= in this URL is the post ID.
Create a New WordPress Post
The function CREATE_WP_CONTENT creates a new post on your WordPress site.
=CREATE_WP_CONTENT(E3:E6,F3:F6,"posts")
In the "content" field, you can also include HTML to present content better as shown in the example below. You can even include CSS and Javascript as a body of the post.
<p> This is an introductory paragraph for testing. </p>
We have a function called CONVERT_TO_HTML to convert data to HTML table.
It considers headers in cells E3:E6 and values in cells F3:F6 to create a post as shown in the table above.
- title: The name of the post.
- content: The main body of the post including text, images, videos etc.
- slug: A URL-friendly version of the title, used in the post's link.
- status: The visibility of the post like publish, future, draft, pending, private.
- date: The date the post was published, in the site's timezone.
- date_gmt: The date the post was published, in GMT.
- password: A password to protect access to the content and excerpt.
- author: The ID of the author for the post.
- excerpt: A brief summary or excerpt of the post.
- featured_media: The ID of the featured media for the post.
- comment_status: Whether or not comments are open on the post. Options include: open, closed.
- ping_status: Whether or not the post can be pinged. Options include: open, closed.
- format: The format of the post, such as standard, aside, chat, gallery, link, image, quote, status, video, or audio.
- meta: Additional meta fields for custom data.
- sticky: Indicates if the post should be treated as "sticky" (featured).
- template: The theme file used to display the post.
- categories: The categories assigned to the post.
- tags: The tags assigned to the post, under the post_tag taxonomy.
Update WordPress Post
The function UPDATE_WP_CONTENT modifies an existing post of your WordPress site.
=UPDATE_WP_CONTENT(E3:E6,F3:F6,357,"posts")In this example, we are updating post with ID 357.
Delete WordPress Post
The function DELETE_WP_CONTENT deletes a specific post of your WordPress site.
=DELETE_WP_CONTENT("posts", 774)In this example, we are removing post with ID 774.
Pages
List WordPress Pages
The following function returns details of pages from your WordPress site.
=GET_WP_CONTENT("pages")If you want details of a specific page, you can specify id in the second argument.
=GET_WP_CONTENT("pages",711)Create a New WordPress Page
The following function creates a new page on your WordPress site.
=CREATE_WP_CONTENT(E3:E6,F3:F6,"pages")It considers headers in cells E3:E6 and values in cells F3:F6 to create a post as shown in the table below.
| E | F | |
|---|---|---|
| 3 | title | My New Title |
| 4 | content | This is introduction to using Wordpress API in Google Sheets |
| 5 | slug | wp-in-sheets |
| 6 | status | publish |
- title: The name of the page.
- content: The main body of the page including text, images, videos etc.
- slug: A URL-friendly version of the title, used in the page's link.
- status: The visibility of the page like publish, future, draft, pending, private.
- date: The date the page was published, in the site's timezone.
- date_gmt: The date the page was published, in GMT.
- password: A password to protect access to the content and excerpt.
- author: The ID of the author for the page.
- parent: The ID The ID for the parent of the page.
- excerpt: A brief summary or excerpt of the page.
- featured_media: The ID of the featured media for the page.
- comment_status: Whether or not comments are open on the page. Options include: open, closed.
- ping_status: Whether or not the page can be pinged. Options include: open, closed.
- meta: Additional meta fields for custom data.
- template: The theme file used to display the page.
- menu_order: The order of the page in relation to other pages.
Update WordPress Page
The function UPDATE_WP_CONTENT modifies an existing page of your WordPress site.
=UPDATE_WP_CONTENT(E3:E6,F3:F6,451,"pages")In this example, we are updating page with ID 451.
Delete WordPress Page
The function DELETE_WP_CONTENT deletes a specific page of your WordPress site.
=DELETE_WP_CONTENT("pages", 818)In this example, we are removing page with ID 818.
Media Items (Images)
Get Media Items from WordPress
The following function fetches a list of media items (images/videos) from a WordPress site.
=GET_WP_CONTENT("media")You can specify id of media item in the second argument if you want details of a specific image or video.
=GET_WP_CONTENT("media",761)Delete Media Items from WordPress
The following function deletes a specific media item like image from the WordPress media library. In this case, we are removing a media item with ID 804.
=DELETE_WP_CONTENT("media", 804)Comments
Get Comments from WordPress
The following function extracts comments from a WordPress site.
=GET_WP_CONTENT("comments")The following function returns a comment with ID 5 from your WordPress site.
=GET_WP_CONTENT("comments",5)Delete Comments from WordPress
The function DELETE_WP_CONTENT deletes a comment from your WordPress site.
=DELETE_WP_CONTENT("comments", 5)Create a New Comment
The following function creates a new comment on your WordPress site.
=CREATE_WP_CONTENT(E9:E12,F9:F12,"comments")| E | F | |
|---|---|---|
| 9 | post | 351 |
| 10 | content | this is a new comment |
| 11 | author_name | deeps |
| 12 | author_email | deeps@gmail.com |
Update Comment
The following function modifies an existing comment on a WordPress site.
=UPDATE_WP_CONTENT(E9:E12,F9:F12,10,"comments")How to Send Table From Google Sheets to WordPress
Let's say you have a table in cells B3:C7 and you want to transfer data from the table to your wordpress site.
You can use the function below to generate table's HTML source code.
=CONVERT_TO_HTML("B3:C7")Now you can use the table's HTML source code in the "content" field in the CREATE_WP_CONTENT or UPDATE_WP_CONTENT functions.
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Share Share Tweet