Joomla 4 – Joomla API
Present slides https://slides.woluweb.be
At the end of this presentation, you will be able a.o. to import your Articles together with their Custom Fields to your Joomla website directly from a Google Sheet thanks to the Joomla API
This presentation was made
- on Tim Davis’ BasicJoomla YouTube Channel | 2022.11.23 | video (English)
1. Please contribute
Help improving this presentation that will be converted to a Joomla Community Magazine article:
- if you think of other tips & tricks
- if any information can be more precise
- if you know of any other resource
- if you spot any typo
- if you see any error
- …
then please get back to me! ❤️💙🧡💚
1. How this presentation came to life
There have already been several presentations about the Joomla API.
They were certainly interesting (showing Postman, explaining how you can create a Smartphone App exchanging with your Joomla website, …) but I was always a bit frustrated because :
- the target of these session was more developers or people who are already play with APIs
- there was never a basic example or a step-by-step procedure allowing to “lower the threshold”
This changed on 23 September 2022 where I had the chance to be present at JoomlaDay D-A-CH (gathering several german-speaking countries, namely Germany – Austria – Switzerland). In particular Peter Martin gave an excellent “How to use APIs in your Joomla website?” (see links hereafter): At the end of his session, Peter would give a few little PHP scripts showing how to create or update Articles.
I asked to Peter whether he could share his scripts. He did even better: there is a new official website Joomla! Programmers Documentation with a page about Web Services and Peter made a Pull Request on Github to add the examples (see hereafter).
Coming back to Belgium, I was really excited to play with these examples.
Then I contacted Alexandre Elisé from Martinique, who is very passionate about the Joomla API and we elaborated on these examples. Indeed
- I had ideas of real usecases for the Joomla API
- and Alexandre had the coding skills to bring those ideas to life
So after several iterations over a few weeks, we ended up with a series of “little” scripts. The goal was indeed
- to give a flavour of what Joomla API can bring
- to provide short and easy-to-customize scripts (I like to say “one-page scripts” since in practice they have only about 200 to 300 lines)
- to have scripts which are independent
- note though that they could be easily adapted to use the Joomla Framework… but then it would become an installable extension and this goes beyond the scope of this initiation
- the advantage of an independent script is that it can even run on another server, be triggered by a cron task (from your server Panel… or from the native Joomla Task Scheduler introduced in Joomla 4 as well)
So a long story short: this presentation aims at allowing a maximum of people to start using the Joomla API, which offers a strong competitive advantage compared to other CMS for example.
2. Resources
Peter Martin – “How to use APIs in your Joomla website?” at JoomlaDay D-A-CH on 2022.11.23
- https://dach.joomladay.org/programm-2022/programm-samstag-2022/how-to-use-apis-in-your-joomla-website
- https://www.youtube.com/watch?v=AXytRiEkjVc
- https://petermartin.nl/en/presentations/joomla/how-to-use-apis-in-your-joomla-website
- https://slides.db8.nl/md/22-joomla4-api.html
Alexandre Elisé
- Alexandre’s blog: https://apiadept.com/blog-joomla-specialist
- API collection for Postman: https://github.com/alexandreelise/j4x-api-collection
- Series of scripts showcasing the Joomla API: https://github.com/alexandreelise/j4x-api-examples
When you go to https://github.com/alexandreelise/j4x-api-examples please click on the Star button if you appreciate Alexandre’s scripts & contribution :
Joomla! Programmers Documentation (in construction)
- https://manual.joomla.org/docs/general-concept/webservices
- Peter Martin’s PR: https://github.com/joomla/Manual/pull/41/commits/647588e4f90ee692b731cb468a3037eff4df24c1
- Alexandre Elisé's improvement on that: https://github.com/alexandreelise/Manual/blob/patch-1/docs/general-concept/webservices.md
Joomla! Documentation
- Adding an API to a Joomla Component https://docs.joomla.org/J4.x:Adding_an_API_to_a_Joomla_Component
3. How to get the Joomla API Token
Before using the Joomla API (and any of the scripts below) we need a Joomla API Token.
At the moment, the Joomla API Token can only be generated for Super Users
.
So be sure to keep it secret because any person having somehow access to that Token is de facto a Super User
and can do everything on your website, including deleting everything.
Note: as far as I know, it is the idea at some point to allow non Super Users
to access the API (which is necessary if you want to give a Token to a third-party who only wants to fetch data for example).
[EDIT] On 2022.11.25 Alexandre shares this additional information on Facebook:
“You don’t need to be a Super User to access the Joomla Api. You can create a Web Services usergroup for example, go to Global Configuration and give this Group Permissions for API and site login to be able to see the token. You need also to add the Web Services group you just created to the list of allowed groups in the token user plugin config. But for now you still cannot have fine-grained ACL and Access Level per endpoint.For more security you could also disable sensitive webservices plugins like the users web services plugin but then the consumer app using the token cannot manage user info. I haven’t tried it yet, but I think if you give specific permissions to the Web Services usergroup then add only app user in this group you could achieve want you want even if it’s still not per route / endpoint.”
“Just did a quick test on a fresh joomla 4.2.5 install you don’t need to be Super User. morever Joomla seems to already handle permissions per endpoint but only for core endpoints. At least that’s what I’ve tried. that means that if you create for example a web services group to manage all consumers of your Joomla web services and then deny create patch delete users for this usergroup the app/consumer of the joomla web services will get a 403 Forbidden http status code when trying to perform denied actions.”
Joomla Api Without Super User > Demo video (18 min)
3.1. Create a new User
Actually you could of course create a Joomla API Token for your own account.
But it is probably a good practice to create a distinct user
- you can then for example disable at any time
- you can then see easily in the User Actions Logs who has done what and when (and see whether it was the API or yourself)
3.2. Give a strong password
3.3. Assign to the User Group Super User
3.4. Go the Joomla API Token tab and Read
3.5. Save
Now that you have saved the User, the Joomla API Token has been created… but for security reasons only that given user can see it (nobody else, not even other Super Users).
3.6. Log in as the new User and copy the Token
4. Create, Update, Fetch and Delete Joomla Articles with the API
4.1. Create Joomla Articles with the API
4.1.1. Terminology
Some basic terminology first: When using the Joomla API
- Create =
post
- Update =
patch
- Fetch =
get
4.1.2. See the Articles Manager
This is a fresh website so I see that “No Articles have been created yet”
4.1.3. Prepare the POST script
Create your POST script on your website
- create a new file on your website (for example
api-post.php
at the root) - go to https://github.com/alexandreelise/Manual/blob/patch-1/docs/general-concept/webservices.md
- go to the “Using the PHP cURL Functions” section (because we want here an independent script so we don’t use the Joomla Framework version)
- copy the code for “Define some variables” and paste it in your file
- copy the code for “POST” and paste it at the end of your file
Customize your POST script on your website
- in your file adapt the variable
$url
with your domain name. In my example:- from
$url = 'https://example.org/api/index.php/v1';
- to
$url = 'https://api.joomlacustomfields.org/api/index.php/v1';
- from
- and paste your Token. In my example:
- from
$token = '';
- to
$token = 'c2hhMjU2OjI3NDpmOGMyNGIyMmI4ZTI0YzhlN2VhMGI1YmI2MTk5ODdiODViZWIwYjBhMTkwYzYxMmZkMDEzYWUxNzg5MmE2YzNm';
- from
4.1.4. articletext vs introtext and fulltext
Good to know: when you Create an article
- instead of using (just like in the database of a Joomla website) the classical
introtext
for the text which comes before the potential READ MORE- and/or
fulltext
for the text which comes after the potential READ MORE
- you can use
articletext
which is “smart” and will separate your text intointrotext
andfulltext
when detecting<hr id="system-readmore">
4.1.5. The 4 necessary fields
To create an article
- title (Joomla takes care automatically of the alias creation. Reminder: a given alias can only be assigned to 1 article)
- articletext (see explanation above)
- catid (the category ID. Example : une uncategorized which is available by default on every Joomla installation had ID 2)
- language (that you can set on when you want to assign an article to all* languages)
4.1.6. Execute your script
Simply
- open a new browser tab
- type the full url of your script
- press Enter to open that url
- the script is being excuted
- and finally the page displays some feedback
- this is only to reassure you that everything was fine
- as such, displaying anything is totally unnecessary
- with other words, you could simply comment or delete the line
echo $response;
4.1.7. Go back to or refresh the Articles Manager
Tadam you can see that the Article was indeed created!
4.2. Update Joomla Articles with the API
4.2.1. Prepare the PATCH script
The procedure is totally similar to the one of the previous script:
Create your PATCH script on your website
- create a new file on your website (for example
api-patch.php
at the root) - go to https://github.com/alexandreelise/Manual/blob/patch-1/docs/general-concept/webservices.md
- go to the “Using the PHP cURL Functions” section (because we want here an independent script so we don’t use the Joomla Framework version)
- copy the code for “Define some variables” and paste it in your file
- copy the code for “PATCH” and paste it at the end of your file
Customize your PATCH script on your website
- in your file adapt the variable
$url
with your domain name. In my example:- from
$url = 'https://example.org/api/index.php/v1';
- to
$url = 'https://api.joomlacustomfields.org/api/index.php/v1';
- from
- and paste your Token. In my example:
- from
$token = '';
- to
$token = 'c2hhMjU2OjI3NDpmOGMyNGIyMmI4ZTI0YzhlN2VhMGI1YmI2MTk5ODdiODViZWIwYjBhMTkwYzYxMmZkMDEzYWUxNzg5MmE2YzNm';
- from
Good to know: when you Update an article
- the field
articletext
does not work - you should use (just like in the database of a Joomla website)
introtext
for the text which comes before the potential READ MORE- and/or
fulltext
for the text which comes after the potential READ MORE
4.2.2. Execute your script
Simply execute your script as already explained above.
4.2.3. Go back to or refresh the Articles Manager
When opening the Article, we can indeed check that
- the Title has changed
- as well as the Introtext and Fulltext
4.3. Playing with Custom Fields
Adding a value to some Custom Field of an Article directly in the database using a query in PHPMyAdmin is not so obvious because it involves several Tables which are linked.
But as you will see, adding a value to some Custom Field of an Article via the Joomla API is super easy.
4.3.1. Create a Custom Field
In this example I create a Custom Field of Type “Text”
- its Title is “Temperature”
- its Name (meaning Alias) becomes then automatically “temperature” upon Saving (but I could of course type another Name like “temp” if I wanted)
4.3.2. Adapt the PATCH script
Now I need to edit my api-patch
script as follows
- in the
$data
I add a new line'temperature' => '15 degrees Celcius'
- (and I don’t forget to add
,
at the end of the previous line)
4.3.3. Execute your script
Simply execute your script as already explained above.
You already see on what is displayed that “temperature” is mentioned.
4.3.4. Go back to or refresh the Articles Manager
When opening the Article, we can indeed check that
- the Custom Field “temperature” has been filled in
4.4. Fetch Joomla Articles with the API
A good usecase for this script: an intranet or an app where one wants to display some news (articles) from your Joomla website
4.4.1. Prepare the GET script
Create your GET script on your website
- create a new file on your website (for example
api-get.php
at the root) - go to https://github.com/alexandreelise/Manual/blob/patch-1/docs/general-concept/webservices.md
- go to the “Using the PHP cURL Functions” section (because we want here an independent script so we don’t use the Joomla Framework version)
- copy the code for “Define some variables” and paste it in your file
- copy the code for “GET” and paste it at the end of your file
Customize your GET script on your website
- in your file adapt the variable
$url
with your domain name. In my example:- from
$url = 'https://example.org/api/index.php/v1';
- to
$url = 'https://api.joomlacustomfields.org/api/index.php/v1';
- from
- and paste your Token. In my example:
- from
$token = '';
- to
$token = 'c2hhMjU2OjI3NDpmOGMyNGIyMmI4ZTI0YzhlN2VhMGI1YmI2MTk5ODdiODViZWIwYjBhMTkwYzYxMmZkMDEzYWUxNzg5MmE2YzNm';
- from
4.4.2. Bonus 1: select another Category
In this example, we suppose we want to get all articles from Category 2
Of course, you can easily change the ID of the Category you need by adapting the following line of code:
$categoryId = 2;
4.4.3. Bonus 2: only get Published Articles
What is you only want to get Articles which are Published.
In the database, you probably know that technically being
- Published means that “state” is 1
- Unpublished means that "state is 0
- Trashed means that “state” is –2
- Archived means that “state” is 2
So all we need to change in the script is to change
/content/articles?filter[category]=
into
/content/articles?filter[state]=1&filter[category]=
4.4.4. Execute the script
Simply execute your script as already explained above.
4.5. Delete Joomla Articles with the API
Create your DELETE script on your website
- create a new file on your website (for example
api-delete.php
at the root) - go to https://github.com/alexandreelise/Manual/blob/patch-1/docs/general-concept/webservices.md
- go to the “Using the PHP cURL Functions” section (because we want here an independent script so we don’t use the Joomla Framework version)
- copy the code for “Define some variables” and paste it in your file
- copy the code for “DELETE” and paste it at the end of your file
Customize your DELETE script on your website
- in your file adapt the variable
$url
with your domain name. In my example:- from
$url = 'https://example.org/api/index.php/v1';
- to
$url = 'https://api.joomlacustomfields.org/api/index.php/v1';
- from
- and paste your Token. In my example:
- from
$token = '';
- to
$token = 'c2hhMjU2OjI3NDpmOGMyNGIyMmI4ZTI0YzhlN2VhMGI1YmI2MTk5ODdiODViZWIwYjBhMTkwYzYxMmZkMDEzYWUxNzg5MmE2YzNm';
- from
We don’t provide screenshots here. By now you know how it works!
5. How to launch any of the scripts automatically
Given the fact that our scripts here are independent of Joomla
- we can put them on our own website
- or even on another server (if we trust its owner of course, since being able to see the Token means being a Super User)
Lauching any of the scripts can be simply done:
- by opening the corresponding URL in a browser
- either directly
- either for example thanks to a nice button in the backend
- by calling this URL in any piece of code (PHP or other)
- by setting up a Cron job calling this URL
- either via the tool provided by your host (free)
- either via some online tool providing cron tasks (in general paid)
- by setting up a Task Scheduler in Joomla
So obviously it means that you can automate the execution of the creation / update of your Joomla Articles at any time interval.
5.1. Example 1: via a nice button in the backend
In Joomla 4 it has become even easier than before to create a Custom HTML Module in the backend.
Take advantage of this feature to create a nice interface to your Users.
5.2. Example 2: via Joomla Task Scheduler
The Task Scheduler is a new feature introduced with Joomla 4.
- Go to System > Schedule Tasks
- Click on the New button
- Create a Task of Type “GET Request”
- Configure it (put an absolute link for “Request URL”. Example: https://www.MYDOMAINNAME.com/api-blabla.php)
6. Be the only one who can run your scripts
If somebody know that you have a script called for example api-test.php
, this person could flood your websites with request on that url.
A typical way to avoir that is on Apache servers is to create a htpsswd, meaning you have to
- add a few lines in your .htaccess
- add a .htpsswd file, to which your .htaccess lines will refer with the correct path (for exemple
/YOUR_PATH/public_html/.htpasswd
)
When a htpsswd is enabled, you get a popup asking for the defined username & password before accessing.
You can typically
- either use your Host tools to generate such an .htaccess rule
- or use an online generator (like https://hostingcanada.org/htpasswd-generator)
But we need to be more specific because
- we only want to block our scripts, not our whole website.
- we only want to block our scripts if they are launched in the browser, not if they are launched by the website itself
- either via some code
- either via the Task Scheduler
Let us first show a good example of such an .htaccess rule:
<FilesMatch "^api*">
AuthType Basic
AuthBasicProvider file
AuthUserFile /YOUR_PATH/public_html/.htpasswd
AuthName secure
<RequireAny>
Require valid-user
Require ip 127.0.0.1 185.221.181.208
</RequireAny>
</FilesMatch>
7. Change the API Token if needed
If you have any reason to think that your API key is not private any more (like me after having shared this presentation) then simply Edit the User in question, go to the Joomla API Token tab and click on Reset
- the old Token is revoked
- a new key is displayed, which you can now paste in all your scripts
If a script having a wrong or revoked Token is called then it will display the following error message: “forbidden”
7.1. How did restrict the rule to our scripts
Let’s suppose that all my scripts filenames start with api
.
Then I want to have this htpsswd popup asking only for the files starting with api
.
This is why in the snippet of .htaccess below we have <FilesMatch "^api*">
7.2. How did we allow the scripts if launched by the website itself
See the line Require ip 127.0.0.1 185.221.181.208
- 127.0.0.1 corresponds to the localhost. In some cases it is enough
- in my case though, when launching my script with Joomla’s Task Scheduler, my script was blocked: I also had to allow explicitely the IP of my server
- Q: So how did I find the IP address of my server?
- A: Simply by checking the IP address given for “A Record” for my Domain Name, with any online tool like https://www.ns-lookup.io/
7.3. More information
For more information about this kind of .htaccess rules, see https://httpd.apache.org/docs/2.4/upgrading.html
8. Using the Joomla API to Import and Update Articles directly from a Google Sheet
Being able to POST or PATCH a single Article programmatically with a few lines of code thanks to the API as shown above is great… but the added value in real life is quite limited at this point.
Indeed, having to prepare 1 script to create only 1 article would seem quite cumbersome. But once you can do it for 1, one starts to think “what about having a loop to create multiple articles” :)
When I first spoke about this with Alexandre, I would already have been happy to do it with a CSV file:
- export to CSV-format from an Spreasheet
- and then select that file with a kind of import button somewhere
But Alexandre went further:
- why bother exporting/importing a CSV file: if you use Google Sheet for example, you can share it with a simple link… to its CSV version
- why importing Articles only into 1 website: let’s have a column in our Google Sheet to select for each row in which website we want to create the corresponding Article
- why limit the import to the native Fields, let’s make it easy to also import (any number of) Custom Fields
- why not allow to also have json in a cell since the following Fields are stored in json-format in the database
- Intro Image & Full Image links, together with their configurations (Description, classes, …)
- Urla, Urlb, Urlc
- Custom Fields of type Subform
- why having to respect some order of the columns in the Google Sheet: let’s allow the user to have any order
8.1. Prepare Google Sheets
8.1.1. Create Google Sheets
Create a Google Sheets
- go to https://drive.google.com/
- click on the New button
- select Google Sheets
- give a Title to the newly create Google Sheets
Now we need to have some sample data with the right structure for our Google Sheets. We don’t want to reinvent the wheel so let’s have a look at the following script:
In particular I see the following line $csvUrl = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vTlM7un4cv3t5oKQ6mymmBDrAnbpFcYLFh6KnHMC6iCE8qUJSNKJ4Vw54r4OjGNNU4DXxOuLWdtKvZ2/pub?output=csv';
Excellent, let’s copy/paste this URL in a browser
https://docs.google.com/spreadsheets/d/e/2PACX-1vTlM7un4cv3t5oKQ6mymmBDrAnbpFcYLFh6KnHMC6iCE8qUJSNKJ4Vw54r4OjGNNU4DXxOuLWdtKvZ2/pub?output=csv
This makes my browser download a CSV file. I could start from this file… but an easier way is to simply display the Google Sheet directly on the browser by dropping the ?output=csv
at the end of the URL.
With other words, I open
https://docs.google.com/spreadsheets/d/e/2PACX-1vTlM7un4cv3t5oKQ6mymmBDrAnbpFcYLFh6KnHMC6iCE8qUJSNKJ4Vw54r4OjGNNU4DXxOuLWdtKvZ2/pub
Then
- I copy/paste the content of this Sheet in my own empty Google Sheets
- I delete the following columns if they are still there (they were actually created at some point only to make more tests with the script)
- alias-seed
- seed
- article-subform-field (which is useful only if you want to import Custom Fields of type Subform which are stored in JSON format in the database)
- for the demo here I only keep the first 3 rows of content
8.1.2. Share Google Sheets
In Google Sheets
- select the Menu File > Share > Publish on web
- on the popup window
- select the Sheet containing your data
- select CSV (and not webpage nor PDF nor whatever)
- click on the Publish button
- copy the URL now displayed in the popup (and paste it somewhere, we will need it later)
- close the window
In our case, the url is
https://docs.google.com/spreadsheets/d/e/2PACX-1vT5Th1TKSKv3vZuEvTnpK_MPqhItjuyV8ierdbNQa9FV2nYWHV78Cd9IRGbP9BHIXPCmnCrs6Jm5791/pub?gid=0&single=true&output=csv
So if you want to have a preview of that directly on your browser, take the url without what follows /pub
, namely
https://docs.google.com/spreadsheets/d/e/2PACX-1vT5Th1TKSKv3vZuEvTnpK_MPqhItjuyV8ierdbNQa9FV2nYWHV78Cd9IRGbP9BHIXPCmnCrs6Jm5791/pub
Screenshots (my computer is obviously is French but it is pretty straigtforward):
8.2. Basic example
8.2.1. Prepare Google Sheets script
Create your Google Sheet script on your website
- create a new file on your website (for example
api-google-sheet.php
at the root) - go to https://github.com/alexandreelise/j4x-api-examples/blob/master/using-raw-php/smart-add-edit-to-multiple-sites-from-csv-url.php
- go to the “Using the PHP cURL Functions” section (because we want here an independent script so we don’t use the Joomla Framework version)
- copy the code for “Define some variables” and paste it in your file
- copy the code for “Google Sheet” and paste it at the end of your file
Customize your Google Sheet script on your website. In your file adapt the following variables
$csvUrl
with the url we just got from Google Sheets$baseUrl
. In my case- I replace
'app-001' => 'https://app-001.example.org',
by'app-001' => 'https://api.joomlacustomfields.org',
(beware, without the trailing slash!)- I delete the lines with
'app-002'
and'app-003'
because I want to import only one 1 website, not on 3 (or more) like the script allows $token
. In my case I replace'app-001' => 'yourapp001token',
by'app-001' => 'c2hhMjU2OjI3NDpmOGMyNGIyMmI4ZTI0YzhlN2VhMGI1YmI2MTk5ODdiODViZWIwYjBhMTkwYzYxMmZkMDEzYWUxNzg5MmE2YzNm',
- I delete the lines with
'app-002'
and'app-003'
because I want to import only one 1 website, not on 3 (or more) like the script allows
8.2.2. Execute your script
Simply execute your script as already explained above.
Normally this script should not even display anything and just run in the background.
Still the script displays the “raw result”: this is clearly not necessary but allows to visually check that something indeed happened (and once I could see that some rows were not processed and could identify the cause in the Google Sheets for example).
Beware: I have already noted that Google Sheets sometimes take 1 minute or so before actually updating the CSV version (even if other users of the Google Sheet see your changes “live”). So if after executing your script you see no difference, simply wait 1 minute and relaunch it
8.2.3. Go back to or refresh the Articles Manager
When opening the Article Manager, we can indeed check that
- new articles have been created. Tadam!
And when opening any of them, we can also see that everything is there
- introtext of the article
- link / description / class / caption for Intro Image and Full Image
- urla, urlb, urlc
Note:
- in this case, for the Intro Image and the Full Image we simply put an link to an external image in the Google Sheets
- in real life, one would probably want
- either to use Joomla’s Media Manager to select the images
- or use something more powerful than Google Sheets, like AirTable which allows to put images in a “cell” (see the AirTable script which is even capable of physically copying the Airtable images locally to the website)
8.2.4. If you want to Update your articles later on
You have probably noted that the first column in the Google Sheet was id
, being of course the ID for each article.
Actually we wanted one single script which works both to Create and to Update Articles.
But how to make the script “smart” enough for that?
- if the
id
is 0 then the script will Create an article - if the
id
is any other number, it will Update the article having the corresponding ID
So at this point, if there is a chance that I execute again this script in the future, I don’t want to re-create the already existing articles.
Therefore, in my Google Sheet
- I put manually the
id
of each already created article - and if I wish to create new articles next time I run the script, I can also do it by giving them the
id
0 - and so on each time I run the script
Illustration with my adapted Google Sheet where
- I change the ID, the Title and the Alias for the first 3 articles
- I add a new row to create a new article
And the result in the Articles Manager after running the script again:
Obviously in your Google Sheets you can change the value of any field. Example: language is set on *
(meaning all languages since it is the way it is encoded in the database) but you could specify a language like fr-FR
8.2.5. Two rounds to process
The script runs two loops :
- first it it creates all the article it is asked to create
- but if the required alias is already taken it cannot create the article
- then it remembers that
- and once the first loop is finished it makes a second loop in order to create those articles, by adding a random number to the required alias (in the code see the section // Handle errors and retries)
8.2.6. Other smart features
Note that the script has also been made as “smart” as possible for different aspects. Examples:
- if there is an error for a given row, the script will simply skip that row but go on with the next ones. Examples
- there is a Custom Field of type Link but in the corresponding cell the User has types Text or types an email address
test@test.com
but forgets to mention the protocolmailto:
) - some required data are missing
- there is a Custom Field of type Link but in the corresponding cell the User has types Text or types an email address
- as you might know, in a given category an alias can only be given once
- so if a certain alias is already given, the script will add a random number to the proposed alias so that it can still be processed/created. Illustration:
8.3. Advanced example
The following steps are not necessary of course: it only applies if you intend to use the script for example
- to import other native Fields like
publish_up
,publish_down
,featured_up
,featured_down
(to find the right name for these fields, simply look at the database columns…) - to import Custom Fields
8.3.1. Create Custom Fields in Joomla
Let’s suppose we create a website for a Rock band. It would be much easier for the musicians to add their new concerts simply in a Google Sheets than to lean how to use a CMS.
Those concerts would then be easily imported and/or updated in the website.
So let’s create a new Category Concerts
and assign to it the 3 following Custom Fields :
- Date and Time: CF of Type Calendar with name
date-and-time
- Venue: CF of Type Text with name
venue
- Link to Event: CF of Type URL with name
link-to-event
8.3.2. Adapt the Google Sheet
Since we create a new Category (“Concert” with ID 8), let us change the value of the catid
column from 2 to 8 in the Google Sheet.
Let us also create the following columns in the Google Sheet:
publish_up
,publish_down
,featured_up
,featured_down
date-and-time
,venue
,link-to-event
Note: when you type date, they should have the following format: 2022-11-23 20:00:00
(and visibly it imports dates in the real TimeZone of your website, as if you were typing them from Joomla’s backend)
Here is an example with some values:
8.3.3. Adapt the script
In your script
- add the custom fields by changing
$customFieldKeys = [];
into$customFieldKeys = ['date-and-time','venue','link-to-event'];
- and add the following block inside the
$defaultKeys
array:
'publish_up',
'publish_down',
'featured_up',
'featured_down',
8.3.4. Go back to or refresh the Articles Manager
Tadam!
Our Categories have indeed been Updated:
Our Custom Fields have indeed been Updated:
Our other native Fields have indeed been Updated:
8.4. Special fields like Images or Custom Fields or Type Subform or URLs
Some special fields entails “multiple values”. So how to handle this?
Suppose you want to import an image or a Custom Field of Type Subform.
As you might be aware, in the database these fields are saved in json format.
So what should we type in our Google Sheet cell ? The easiest way is to save a real Article on the website and see in the database what the corresponding json is.
Note that we don’t need in the Google Sheet to “escape the /
character”. With other words we simply type /
and not \/
Example for Image (which includes the Intro Image and the Full Image, together with their ALT, Caption etc):
{"image_intro":"images/test.jpg","image_intro_alt":"","float_intro":"","image_intro_caption":"","image_fulltext":"images/test.jpg","image_fulltext_alt":"","float_fulltext":"", "image_fulltext_caption":""}
Example for a Custom Field of Type Subform having in this case 2 Custom Fields (field2 and field7) and 3 values (row0, row1, row2) – note: it also accepts HTML tags but I can’t paste them here in the presentation:
{"row0":{"field2":1410,"field1":"This an article summary 1 for this dessert. Short & Sweet!", “field7”: “What’s up Super Joomlers! Alex here…Proud to be a joomler. Nowadays focusing on Joomla! 4.x Web Services Apis.”}, “row1”:{"field2":1410,"field1":"This an article summary 2 for this dessert. Short & Sweet!", “field7”: “What’s up Super Joomlers! Alex here… Proud to be a joomler. Nowadays focusing on Joomla! 4.x Web Services Apis.”}, “row2”:{"field2":1410,"field1":"This an article summary 3 for this dessert. Short & Sweet! “, ”field7": “What’s up Super Joomlers! Alex here… Proud to be a joomler. Nowadays focusing on Joomla! 4.x Web Services Apis.”}}
Example for the urls Field (those URL A, URL B and URL C which are native in Joomla):
{"urla":"https://alexandree.io","urlatext":"Website","targeta":"","urlb":"https://github.com/alexandreelise", "urlbtext":"Github “,”targetb":"","urlc":"https://twitter.com/mralexandrelise","urlctext":"Twitter", "targetc":""}
Note: my Editor is tweaking my code snippets here. Of course all the " are regular double quotes (and not italic). And the Github icon is also added by the Editor
8.5. Fields with potentially multiple values like Custom Fields of Type List or Checkbox
Suppose you have a Custom Field of Type List or Checkbox. For those CF
- there is typically an Option to allow or forbid Multiple Values
- when you create your proposed options, for each of them you specify
- a Text (ie what will be displayed in the front-end and in the back-end)
- a Value (ie what is written in the database)
In that case
- if don’t have Multiple Values
- in the Google Sheet you should simply type the unique Value (example: chocolate)
- if you have Mutiple Values
- in the Google Sheet you should then type {"0":"fries", "1":"chocolate", "2":"beer"} (without spaces) if the 3 values that I want are fries, chocolate and beer
Note: at the moment the script might throw the following messageCannot use object of type stdClass as array
but apparently this does not prevent the code from executing and the content from being imported
- in the Google Sheet you should then type {"0":"fries", "1":"chocolate", "2":"beer"} (without spaces) if the 3 values that I want are fries, chocolate and beer
8.6. Fields with potentially multiple values like Custom Field of Type Articles Field by Regular Labs
Articles Fields by Regular Labs is a powerful Type of Custom Field allowing to select other Articles: https://regularlabs.com/articlesfield
A good example : for a Library website you have typically have a Category “Books” and a Category “Authors”. You could then link each Book Article to one or several Author Article(s).
If you have multiple values, I have tested succesfully the following 2 methods (let’s suppose we want to link to Articles having ID 12 and 13):
- in the Google Sheet, type 12,13 then Synchronize. After that Edit the Article in the Backend (the 2 values do appear there but are not yet correctly saved in the database) and Save it
- in the Google Sheet, type directly the values in JSON-format, ie {"0":"12", "1":"13"} (without spaces), then Synchronise
8.7. Formula in Google Sheet to transform comma-separated values into the desired JSON
[ added on 30.03.2023 ]
Suppose you have multiple values on the Google Sheet in the form of comma-separated values
12,Se,Lan, DisIn order to import them we need to transform them in JSON-format as explained above:
{"0":"12", "1":"Se", "2":"Lan", "3":"Dis"}