Integrate Amazon DynamoDB with Google Sheets

Amazon DynamoDB is a fast and flexible fully managed NoSQL database service with predictable performance. It is designed to handle the most common data storage patterns, match the pace of modern development and scale cost efficiently.

Google Sheets is a Google spreadsheet application which allows users to manipulate, analyze and visualize data. The application has a variety of features that make it useful for businesses, such as the ability to share spreadsheets and collaborate with others in real time.

Integration of Amazon DynamoDB and Google Sheets is beneficial for both – Amazon DynamoDB and Google Sheets. Integration can be achieved using an open source library called PynamoDB-to-Sheets. This library allows users to seamlessly transfer data from Amazon DynamoDB to Google Sheets. Using this library, users are able to move data from DynamoDB to Google Sheets in batch or single operation. The library is also non-cached meaning that all requests, including large ones, are served directly by AWS. Step-by-step instructions for integration follow:

Create an account on Amazon Web Services and Google Cloud Platform if you don’t have an account already. (You can create a free trial account on both platforms. Go to the official Github repository of PynamoDB-to-Sheets and click on ‘Clone or Download’ button and then click ‘Download Zip’ option. Examine the contents of the zip file. There will be a folder named ‘pynamodb-to-sheets-master’ inside the zip file. From the command line, navigate to the folder ‘pynamodb-to-sheets-master’ and run the command ‘python setup.py install’. Using your preferred text editor, open the file ‘configuration.json’ in directory ‘configs’. Change the value of tableName from ‘test_table’ to your desired table name in DynamoDB. Change the value of serviceName from ‘aws’ to ‘gcp’ if you want to connect to your GCP project instead of your AWS account. Add your credentials for AWS (access key ID, secret access key. to configuration.json under the subheading ‘aws’ by replacing values associated with accessKeyId and secretAccessKey respectively. You can use either IAM roles or credentials file for authentication when connecting to GCP using AWS SDK for Python . Copy the sample credentials file located in directory ‘configs/credentials_file’ to ‘~/.aws/credentials’ in Linux or Mac OS X or ‘%userprofile%/.aws/credentials’ in Windows. Make sure that you change the values of region and output_bucket_name in configuration.json according to your settings. Create a new table in DynamoDB using AWS CLI tool by running the following command:

"aws dynamodb create-table --table-name <table name>"

Open either an existing Spreadsheet in Google Sheets or create a new one by clicking File->Make a new spreadsheet in Google Sheets. Change the value of ‘Destination Table’ under Advanced Settings tab in Google Sheets to match the name of your table created in DynamoDB. Select all data in your Spreadsheet(s. by clicking on the first cell and then pressing CTRL+A or CMD+A on Mac OS X. Click File->Google Drive->Export Unformatted Data… Under Destination tab, select your newly created table in DynamoDB and click OK. If you get an error message similar to one shown below, you may need to refresh your browser cache after exporting data from Google Sheets:

It should be noted that exporting data from Google Sheets into DynamoDB requires at least 700 MB of available space in your output_bucket_name bucket. While this step may consume some time depending on how much data you have exported, you can view progress of export operation by viewing output in CloudWatch. The last step is to import data from DynamoDB into Google Sheets by running the following command:

<path to python>/pynamodb2sheets -i -r -u <your spreadsheet email> -f <bucket> -t <table name> -w <worksheet name> --no-formula --no-duplicate --progress --no-header

The benefits of Integration of Amazon DynamoDB and Google Sheets are as follows:

The process to integrate Amazon DynamoDB and Google Sheets may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick solution to help you automate your workflows. Click on the button below to begin.