This article is part of a series of tutorials to use NationBuilder API. We wrote them in collaboration with Campaign Gears. In the previous article, we explained how to retrieve people information via NationBuilder API. This article explains how to use OpenRefine to bulk update information via NationBuilder API.
Don't hesitate to contact us if you have questions or need help to implement NationBuilder data integration project.
Prerequisite.
For this tutorial like on the previous one you need to know and replace values for:- NATION_SLUG: name defined in NationalBuilder when you create your profiles in https://nationbuilder.com/create.
- NB_TOKEN: value defined by NationalBuilder when the new account is created. The token provides the credentials access to use NationalBuilder API.
You need to install cURL for windows. You can refer to this tutorial.
In this example, we:
In this example, we:
- Prepare the data by downloading information from NationBuilder API,
- Edit them in OpenRefine,
- Upload our changes via NationBuilder API.
Prepare the data
- Follow the steps from the Using cURL method described in the previous tutorial.
- Reorganize, delete and rename columns to better structure and order for the dataset.
- To edit the name of one column: Click in the icon of the column ''Edit column'' -> ''Rename this column''.
- To move column: Click in the icon of the column ''Edit column'' -> ''Move column...''
- To delete column: Click in the icon of the column ''Edit column'' -> ''Remove this column''
- Do the following edit:
- Rename the column ''_ - id'' to ''id'
- Rename the column ''_ - phone'' to ''phone''.
- Move the ''first_name'' and ''last_name'' to the beginning for identifying each person.
- In the phone column, we need to distinguish between empty and null value. Changing the data type from number to text let us format the phone number correctly.
- Click on column ''phone'' option ''Edit cells'' -> ''Common transforms'' -> ''To text''.
- Using the edit option next to each cell, make changes to last_name and phone.
Update the records using NationBuilder API.
The following steps explain how to prepare the cURL command to update the changes we made to the last_name and phone.
- From the column ''id'' select ''Edit column'' -> ''Add column based on this column...''
- In the new window paste the following GREL expression. Set the name for new column to ''cURL_command'' and press ''OK''. Don't forget to update the NATION_SLUG and NB_TOKEN."curl -X PUT \"https://NATION_SLUG.nationbuilder.com/api/v1/people/"+value+"?access_token=NB_TOKEN\" -H \"Content-Type: application/json\" -d \"{\\\"person\\\":{\\\"phone\\\": \\\""+jsonize(cells["phone"].value) +"\\\", \\\"last_name\\\": \\\"new_last_name\\\"}}\""Above expression update ''phone'' and ''last_name'' based on the value in the column "phone" and "last_name"We use ''\' to escape special characters from the GREL expression and for cURL as follow:
- ''\"'' is used for escaping character ''"'' from the GREL expression.
- ''\\\"'' is used for escaping ''\"'' when you run the cURL command.
- The GREL expression handles the Null value as the full-text null. Replace the string name null to valid null values. Click on column ''cURL_command'' and ''Edit cells'' -> ''Transform...'' and paste the next expression:
replace(value,"\\\"null\\\"","null") - To export the new column to a file and execute it like a batch file press on the top right button ''Export'' -> ''Templating...''
- Prepare the template:
- Delete all content from Prefix, Row Template, Row Separator, Suffix.
- Paste the following expression in the template in the area ''Row Template''. Do not forget to copy the extra line-break in the code.
{{cells["cURL_command"].value}} - Press ''Export''.
- Change the extension from the downloaded file from ''.txt'' to ''.bat''. In our example, ''NationBuilder.txt'' become ''NationBuilder.bat''
- Open a terminal in the same location of the file and run the batch file using below command: NationBuilder.bat
