How To Tweak The Natural Language API script in G. Sheets To Extract Entity / Sentiment For Your Data
Doing this blog post as a reader asked for help with customizing the NL API setup in Google Sheets script editor. The original NL API script was built by Alicia Williams @ Google. Link to Github. With the AirBnb example, it uses specific columns from the Google Sheet to process Entity/Sentiment via Natural Language API.
What happens when your data is slightly different? While it’s quite difficult to predict every scenario, I’ll run through some basic steps in the script to be able to adjust it to work with your data. Let’s say your new data looks like this: https://docs.google.com/spreadsheets/d/1lL4rAHRn6QXUBxlLP2X6ve8GFB4Y3807nsVMot_Xe_E/edit?usp=sharing
The data goes into the input sheet while the Entity & Sentiments are classified in the output sheet.
The text that needs to be processed is in paragraph_text column while the URL field is the ID. entity_sentiment automatically marks the row as complete once the API has processed the text in paragraph_text to avoid running the query over such rows again. Let’s start with the adjustments.
In the Script Editor, we’re only going to use columns that are relevant to us. title field will not be processed and doesn’t need to be defined.
As our data is in input and the processed info will go in output sheet, we use the right sheet names. Note: IF and output sheet doesn’t exist, it will create one.
The defines the headers that will be used in the output sheet with the URL field being the equivalent of an ID field.
You now need to only find the relevant columns to use for processing.
This section will go through each row and 61/62/63 will define which columns to use.
Line 91 is where you’ll enter your top secret API key.
That’s it. Run the script via ML Tools > Mark Entities and Sentiment and the row should get marked as complete in the entity_sentiment column.
That’s it. Your output sheet should now have processed the data.
This is just a basic example but will hopefully help anyone who’s looking to tweak the NL API script to make it work with your columns.
