Putting it all together – authenticate, read and update WordPress from a spreadsheet

This is my fourth post in my series of how to build a script to manage WordPress data via the API and a spreadsheet. If you have arrived here without looking at the others you probably should start at the beginning here.

In this tutorial I’m going to handle updating and then pull it all together ( read / authenticate / update ) into a script that initially manages Yoast titles via the spreadsheet.

Something to bear in mind, it that Google Scripts has a maximum execution time of around 5 minutes, whilst reading data into the spreadsheet from my servers I can read thousands of posts in a minute or so, for updating, on my servers I can only get about 500 updates called in the 5 minute before time out. In this script I’m not trying to be clever on timeout / restarting as you can do that manually, but it is possible with timed triggers – but I leave that for advanced posts.

As we have already built the getPosts function in post one, and modified it for meta in post two, and done the basics for JWT authorisation in post three I will concentrate on the updating posts first .

First of all we need a way to know a cell has changed, as we don’t want to scan maybe thousands of rows, trying to update things that haven’t actually changed.

The way I tackle this, is simply to set the background colour of a changed yoast title cell to red, this gives us a nice visual clue, and later when it has been updated set the background to green. This also enables you to re run the script if it breaks ( e.g. timeout ) as the process will pick up on the remaining red cells. This is easy to set using the reserved onEdit(e) function as follow:

And of course we need a menu item to run the updates from, this time we won’t create a separate menu item as if we want to put a script into the chrome web store as a spreadsheet add on (even for your won G Suite domain) , you can’t have a top level menu. It as to be under the add-ons menu.

This site is free to use, but hopes to cover some costs through affiliate income, some products and links are affiliates and may earn the site advertising income.

Some affiliates use Cookies to track if you purchase from them, this allows them to apportion revenue to us you will need to refer to their specific privacy notices as to how you are tracked.

This site is a participant in the Amazon EU Associates Programme, an affiliate advertising programme designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.co.uk.

This website uses cookies to improve your experience. Also when you click on affiliate links those links may use Cookies to track you to be able to apportion revenue to us. We'll assume you're ok with this, but you can opt-out if you wish.AcceptRead More

Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are as essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are as essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.