How to Bulk Delete Sitemaps in Google Search Console
Backstory
So first of all you might be wondering why you want to bulk delete sitemaps in Google Search Console or even batch bulk delete hundreds or thousands of sitemaps in Google Search Console.
Well in our case the answer to this question was that somehow the Google Search Console account had been compromised with thousands of sitemaps that did not belong to the domain.
How Search Console was compromised
We can’t be 100% sure but we suspect that a DNS record was left pointing at a Digital Ocean droplet that had been “destroyed” and removed from the account. It looks like what has happened is some unscrupulous wretch had detected the DNS was still pointing at the server and validated themselves to upload sitemaps.
Let this stand as a lesson. Monitor your DNS records and always remove those pointing at IP addresses you don’t still own
There were no unknown users found at the time we encountered the issue but there were multiple admins so it’s possible somebody had cleaned up but did not know the sitemaps were invalid ones.
How to remove sitemaps in bulk. Two solutions to batch remove.
Solution 1: Browser Automation (Failed)
Before we set about creating our own solution we obviously did some Googling to see if anybody else had this issue and had found a suitable solution. There was only one article “How Automatically Delete Sitemaps Bulk Within Google Search Narula” and just like we’re doing referenced the lack of information on the internet or methods for doing this.
We set about trying the method in the article using a browser automation tool, and a scraping tool.
In fairness, the scraper tool, Scraper, was a great find. I’ve really enjoyed using this tool since finding the article and its come in handy in a number of other tasks. But I didn’t find the automation tool that great.
Problem 1: Browser Automation is too slow
Axiom came with a 2-hour trial. I didn’t really want to sign up for an unknown software early on so I invested some time to set it up and record the automation. The trouble is it was soooooo slow 🐌!! In 20 minutes the script had managed to delete 40 sitemaps.
“No worries,” I thought to myself. I left it running in the background. The only trouble was when I came back from making a coffee, my screen was locked and the script had stopped.
Axiom does come with a whole host of features for handing off to headless or running in the cloud but again I wasn’t really prepared to invest. I knew this was going to be a once-in-a-lifetime task, at best maybe a couple more over the next few years helping other people. Subscribing to another SaaS offering was not on my agenda.
Problem 2: It wasn’t as easy as I thought it would be
I thought it would be as easy to record the automation, but it was not so. The way the Search Console is built means every time the page is loaded all of the IDs are different and my jQuery and JS skills are somewhat lacking. For about half an hour I couldn’t get anything to play in order.
I did find however that if you want to select multiple nested elements in Axiom you click, press the space bar, and then click the next one. I don’t know if this was specific to Search Console and I found no reference to this in Axiom support when Googling.
Summary
I was hoping this was going to be the route to bulk removing hundreds and thousands of sitemaps at once but for me, it didn’t work. Following the instructions, I found on Anubhav Narula post was exact. I couldn’t have asked for more. I think if maybe I had 30-100 sitemaps to delete in bulk this would be the answer but I had 1264. It was going to take far too long and the investment in time and subscriptions was too high.
Here’s what I tried next…
Solution 2: Bulk Delete Sitemaps via Search Console API in under 30 minutes (Success)
So here is my solution to deleting hundreds and thousands of sitemaps in the search console. It’s quick and easy (IMO) and hopefully, you can follow my instructions. If you aren’t familiar with it Google has a Developer Console with hundreds of API services. We’ve used them before to do things like Find emails with no reply automatically in Gmail. Search Console is no different and has an API we can use to bulk delete sitemaps really easily.
Overview Steps
- Generate an OAuth2 token to use in your CLI
- Fetch a list of all of the sitemaps
- Convert the list to a set of delete commands
- Send the delete commands back to Search Console API
In Detail: How to Bulk Delete Sitemaps via Search Console API in under 30 minutes
Total Time: 30 minutes
Generate an OAuth2 token to use in your CLI
To connect to the API from your CLI you will need a bearer token in the header. The great news is there is a super simple way to get this.
Go to https://developers.google.com/oauthplayground/
Choose Google Search Console for the product
Then choose https://www.googleapis.com/auth/webmasters – NOT readonly
Authorize and exchange tokens
This bit is really easy. Just click the blue button “Exchange authorization for tokens”
You will be asked to sign in with your Google account. Make sure its the same one that has full ownership access to the Search Console account you want to work on.
Do a test fetch a list of all of the sitemaps via the API
In the request URI field, we are going to place a URL for the API to request all of the sitemaps associated with the account
https://www.googleapis.com/webmasters/v3/sites/sc-domain:rixxo.com/sitemaps
Something like this where sc-domain:rixxo.com would be the domain property. If you are not targeting a domain property but a web property you would just put the site like www.rixxo.com
Click send the request
You’ll get a response and then you will know that you are calling the correct property and have all the correct credentials.
Copy the Authorization token
You don’t need to do anything with it until the next step but it’s easier if you put this on a notepad now. Everything from and including “Bearer”.
This token is valid for 1 hour so if the next few steps take you longer and it expires just repeat the last step and update the token in the commands in the following steps.
Download a file of all sitemaps in Search Console
Copy the code below and update the Authorization token to match the one in the step previously
curl --request GET \
--url 'https://www.googleapis.com/webmasters/v3/sites/sc-domain:mydomain.com/sitemaps' \
--header 'Authorization: Bearer ya29.a0Aa4xrXPLkS8GB1akmNtFBymsjBI_YXWQvL5juYQHpQGaBVSRi40fQ82Kw1ReKfWYYBKrvRzaEp0JE6aLejv8rVf2jUbS_uIxGyTe9cWCp59Re94gLgf-lxhYmdFEEPM7pp1hRI9BkigDkDTrf2Nodam5yIsNaCgYKATASARESFQEjDvL9fiUGDQsjuo4x6sCm-bLpkg0163' \
--header 'Accept: application/json' > sitemaplist.json
Open Terminal or equivalent and change to the folder you want to save the response in as a CSV file (like Downloads)
Paste your command in your terminal app and hit Enter
A file will download containing the response. This response will be a JSON list of all of the sitemaps and unless you are familiar with JSON it won’t make much sense.
Convert your JSON Sitemap file to CSV
I like to use https://www.convertcsv.com/json-to-csv.htm
Upload your file and choose “convert to CSV”
Download the CSV and open it in your preferred spreadsheet app. We’ll be using Google Sheets.
Prepare your spreadsheet and generate delete commands
Remove everything but sitemap/path column in col1
Add the formula =ENCODEURL(A2) in B2 and copy down the sheet
Add the formula ="curl --request DELETE --url 'https://www.googleapis.com/webmasters/v3/sites/sc-domain:domain.com/sitemaps/"&B2&"' --header 'Authorization: Bearer ya29.a0Aa4xrXPLkS8GB1akmNtFBymsjBI_YXWQvL5juYQHpQGaBVSRi40fQ82Kw1ReKfWYYBKrvRzaEp0JE6aLejv8rVf2jUbS_uIxGyTe9cWCp59Re94gLgf-lxhYmdFEEPM7pp1hRI9BkigDkDTrf2Nodam5yIsNaCgYKATASARESFQEjDvL9fiUGDQsjuo4x6sCm-bLpkg0163'" in B3
Replace the token in the formula with your token. Remember to regenerate it if it’s been a long time since you made it.
Copy your formula down.
Copy and paste a cell in $C to a note page. It should look something like this
curl --request DELETE --url 'https://www.googleapis.com/webmasters/v3/sites/sc-domain:domain.com/sitemaps/https%3A%2F%2Fbranch.domain.com%2FbHZSZ3loOEd5OWVFSTM0UEFKc2lUOWNPa2w1V0pSYTM3V3owSkRndndhMWJzbTc3YytnYkI3R2RYUDlCbCtReA%3D%3D.xml' --header 'Authorization: Bearer ya29.a0Aa4xrXMS71l3NtL7nSfuuZuB-9e8y5LmrmdphjJtACQ1vRZ30AUBNzpKiOcBDdwMf5AQL2Jog30EQ2W4YEX9FdwRzWzAwtbNBO_OWBdnKfPoPnq8sz0ph-dyS8Dwo4IZRwa0mLf4RLTRaUa7X-9_hG5o3dAuaCgYKATASARESFQEjDvL9-2UzpPd_q1ukeHpUmshTlA0163'
Copy and paste commands in batches to bulk delete the sitemaps
The final step to delete your sitemaps in bulk is to paste these commands into your terminal.
A competent developer will be able to turn this into a more elegant solution but for me, all I needed was to delete them quickly.
I copied 1000 rows, pasted them into the CLI and hit enter. There was a bit of a lag with the copy and paste but it managed it. Next time I would maybe do 300-400 sitemaps at a time.
Check all sitemaps are deleted and add any that are required
That should be it. All of your sitemaps will be deleted.
You can now add back in any sitemaps that actually need to be there.
Tools:
- Useful Search Console API instructions if you want to go off and do your own thing: https://developers.google.com/oauthplayground/
- Generate oAUTH2 Token for Search Console here: https://developers.google.com/oauthplayground/
- A cli app like Terminal – I used VSCODE
- Google Sheets or Excel
- Online JSON to CSV Converter https://www.convertcsv.com/json-to-csv.htm
Summary
It might seem complicated but actually, we have avoided making an app to do the OAuth, and I was able to delete 1284 sitemaps in around 30 minutes with this method.
There is very little kicking around on the internet about this subject. I wonder if it’s because this kind of thing doesn’t happen often if people maybe just leave these sitemaps listed as the “appear” to be harmless if unreachable or if this is a new threat and we’ll start to see more of it.
It’s worth saying that at this point you might also want to check your website for vulnerabilities just in case your scenario is not like the one in this article. We’ve put together our list of the Best Static Code & Website Security Scanning Tools that you could use to give your site the once over now you’ve fixed your sitemap issues.
I hope this article is of use. Drop me a line and let me know how you got on with fixing the issue and if you have any improvements to the process.
