By ResearchBuzz
Earlier this week I wrote an article about finding and previewing foreign-language RSS feeds using a Google Sheet I made, the RSSinator. That’s useful but it doesn’t answer an obvious question: how do you translate foreign-language RSS feeds on an ongoing basis?
When I researched methods to do this, I quickly hit a money wall. Zapier, a workflow creator like IFTTT, has a way to do it, but Zapier is expensive for a solo researcher. After a little poking around I gave up and decided to make my own. My first method, using a Google Sheet template, was a miserable failure, so I decided to teach myself Google Apps Script and make a better solution.
And that method worked.
This translation tool is not difficult at all but there are several steps. First I’ll share the script and show you how it works, then I’ll show you how to create a custom IFTTT recipe to aggregate RSS feeds, then I’ll show you how to add the script to your own feed’s worksheet. You’ll need an IFTTT account and access to Google Docs.
Introducing the Translation Google Apps Script
I’ve created a Google Sheet full of items from Spanish-language RSS feeds. You can grab a copy here:
https://docs.google.com/spreadsheets/d/1SGUSh4mJj1UdbOuwdU64648sjIinKLJMs1myNMb9jto/copy
If you’ve copied Google Sheets before, you know you get a screen asking you if you want to copy a shared document. But this time you’ll also get a script warning:
That’s okay, you should expect that since I’ve included the script in the spreadsheet. Click on Make a copy.
After you’ve made the copy, the Google Sheet will open up and look like this:
A few seconds after that, you’ll see a menu item magically appear:
If you click on Translation you’ll see one option: Translate This Feed.
All you have to do is click on that and soon you’ll get a spreadsheet full of translated feeds, right? Well, mostly. First you have to go through the script permission drama.
Script Permission Drama
When you first try to run anything with Google Apps Script in it, Google will give you dire warnings about things going very very wrong. And that’s appropriate – scripts can be really powerful and really mess up your data.
My script is not super powerful. It makes two external calls per feed item to apply Google Translations and it formats the results onto a new worksheet in the Google Sheet. But Google’s not going to assess my script, it’s just going to give you dire warnings.
When you first choose Translate This Feed from the Translation menu, Google warns you that there’s a script incoming:
Click Continue and it’ll prompt you for the GMail account you want to use:
And then it will warn you.
The developer email will look familiar. It will be your email! Because this is a personal script, Google hasn’t verified this and it’s warning you. Click on Advanced. You’ll get one more warning:
In this case YOU are the developer, because it’s a personal script. And I will show you what the script does momentarily. After you’ve read all the warnings, take a deep breath and click on Go to Translate My Feeds (unsafe) to get the last boss-level warning:
Theoretically I suppose my script could access all your stuff and wreak havoc, but I promise you, it doesn’t. Just here to translate. Click on Allow and you’ll be taken back to the spreadsheet. Use the Translation menu and choose Translate This Feed. If you’ve given all the appropriate permissions you’ll get a notice that the script is running:
It will take a minute or two because it’s translating a couple dozen RSS feeds. When it’s done it’ll refresh to a new worksheet that looks like this. Congratulations, you have successfully bulk-translated a sheet of RSS feeds with one click!
The link is the first column, the date of the item is the second, the third is the translated item title, and the fourth is the translated summary. The new worksheet created by the script is called Translated and it can’t be duplicated; if you run the translation script twice in a row it’ll fail the second time because there will already be a worksheet called Translated. Simply rename or remove the worksheet before you run the script again.
So the script works well, as you can see, but what’s happening under the hood? You can take a look by going to the Extensions Menu and choosing Apps Script:
Which will take you to the script editor.
I put comments on my script so you could get an idea of what each section is doing, but you do not need to change any of it, except for (potentially) one line. That line is line 49:
My script auto detects the RSS feed’s language to be translated, so you don’t need to specify that (and you can put multiple languages on one Google Sheet without any problems) but you have to specify what language you want the RSS feed items to be translated into. That’s what the variable lang on line 49 does.
My script’s default is English, but you can put any two-letter language code in between the quotation marks and it should work. I couldn’t test every last language but French (fr) and Japanese (ja) worked fine. If you don’t know the two-letter code you need, Google two-letter language code and the name of the language you want to translate to. If you do change the language code, don’t forget to click the script editor’s save button (
) to save the change before you run the script again!
Now what do we have? We have a spreadsheet full of Spanish-language feeds and we’ve got a script that can translate them all in one click! But where did the feeds come from?
IFTTTT.
Using IFTTT To Gather RSS Feeds
If you’ve been reading ResearchBuzz for any length of time you know about IFTTT. If you don’t, check out this explainer from Pocket-lint. It should give you enough information about IFTTT for this next section to make sense.
In order to create a spreadsheet full of RSS feeds to translate, you need a way to aggregate them. That’s what IFTTT is for. You’ll be using a recipe to put the contents of an RSS feed you specify into a Google Sheet. Then you’ll be adding the script I made to that Google Sheet.
And hey! You don’t even need to make a recipe, because your ol’ pal ResearchBuzz has an IFTTT Pro account. Check out my published recipes at https://ifttt.com/p/researchbuzz .You want the one called “Send RSS feed items to an auto-translating Google Sheet.” The direct URL is https://ifttt.com/applets/rsFeh7Rv-send-rss-feed-items-to-an-auto-translating-google-sheet .
You may need to connect your Google Drive to IFTTT if you’ve never done it before, but once that’s done you should get a template for this recipe:
The first thing you’ll need to add is an URL for an RSS feed. If you don’t have any handy you can use the RSSinator to generate some. Put the URL for the RSS in the Feed URL box.
After that you’ll need to confirm you’re using the right Google Drive account (the account name will appear automatically after you connect Google Drive to IFTTT) as well as give a name for your new spreadsheet and set a drive path so you’ll know where to look for these newly-created files. In my case, I’m calling the new spreadsheet My New Feeds and I’m putting it in the RSS/ directory. Click Save when you’ve finished populating the template.
Note that IFTTT will only fill a spreadsheet up to 2000 rows. After that it’ll start a new sheet. (You can delete old rows you’re not using to maintain the same spreadsheet; as long as your sheet has under 2000 rows populated the IFTTT recipe will keep updating it.)
After that you’ll get taken back to the main applet page with confirmation that your IFTTT recipe has been created!
If you don’t have an IFTTT Pro Account (you don’t need one to use this recipe) then IFTTT will check for new RSS feed items every hour or so. You can use the View activity and Check now buttons to see what your IFTTT recipe has done.
Last night I made an IFTTT recipe to aggregate Spanish-language RSS feeds and let it run all night so I’d have an example set to use this morning. Here’s a sample of what that feed’s activity looks like. It shows you the item found and the action taken, in this case adding lines to a spreadsheet.
Because I know this IFTTT recipe has successfully run, then I should expect to find a Google Sheet called My New Feeds in the RSS/ directory of my Google Drive. And there it is!
Let’s open it up and see what it looks like:
A confession: I created several RSS feeds and directed all of them to this Google Sheet with IFTTT recipes last night to make sure I’d have a large enough sheet to show you how my translation script works. It worked too well and aggregated about 1500 RSS feed items by this morning!
So I abridged this sheet for three reasons: 1) You don’t need 1500 items to see how this works, 2) The script would take ages to run and you might fall asleep, and 3) Google Apps Script has a limit of 5000 calls to Google Translate per day, so a 1500-row spreadsheet would use up a lot of your translation capacity in one click. Instead we’ll use this shorter one. No matter what size the spreadsheet is, all you’ve got to do is add the script you were using before.
If you don’t want to wait for your own IFTTT recipe to populate, you can follow along with this spreadsheet to see how adding a script works.
https://docs.google.com/spreadsheets/d/1WDK2mnIMQtKpK3TnPjkuXyGSRK6QBSCHV20JUb94pGQ/copy
Adding the Google Apps Script
At the beginning of this article I shared a spreadsheet with you called Spanish RSS Feeds. Go back to that sheet and choose Extensions, then Apps Script:
That will take you back to the Apps Script editor:
Make sure that your cursor is in the editing box (that should happen automatically) and click Ctrl+A to copy everything. It should turn blue:
Now Ctrl+C to copy it.
Where do you copy it? Into your My New Feeds sheet. Go to that sheet and choose Extensions, then Apps Script. The scripts editor on this sheet should be empty:
Make sure your cursor is in the script editor and Ctrl+V to paste the script:
You don’t have to change the name from Untitled Project.
Finally, click the save icon (
) and you’ve done it! You’ve added a translation script to your IFTTT RSS feed aggregator, but the Translation menu will not appear until the spreadsheet reloads. Make sure you’ve saved your script, then close the script editor tab and the main spreadsheet tab in your browser.
Now reopen the spreadsheet into which you have pasted your script. After a few seconds a Translation menu item will appear (it won’t be instant.)
Click on the Translate This Feed item under the Translation menu and (after another round of script approval drama) you now have your very own RSS feed aggregator spreadsheet with a script that will translate the feed items with one click!
It’s several steps to set up, but as long as your RSS feeds aggregate on this one sheet (either because you remove old feed rows as you don’t need them or your feeds add items very slowly) you should not have to repeat the script permission process every time you translate. If you would like to create multiple spreadsheets, just copy the script into each one’s Apps Script editor as we just did for the My New Feeds sheet.
Figuring out a way to auto-translate RSS feeds without handing Zapier my wallet took a while, but I’m happy with the way it turned out. I’ll probably spend the weekend beefing up the AutoRSSinator with more languages, then I’ll do a roundup article Monday.
Thanks for reading! And if you support me on Patreon, reach around and give yourself a big pat on the back. It’s because of your support that I have the time and space to figure these things out and share them with you!
April 22, 2022 at 09:14PM
via ResearchBuzz https://ift.tt/ArpJXN7
