Integrate Amazon S3 with Microsoft Excel

Amazon S3

Amazon Simple Storage Service (S3. is a web service that provides object storage in the cloud. It is designed to make web-scale computing easier for developers. It also makes it easier for IT teams to store and manage large amounts of data.

Amazon S3 provides 99.999999999% durability, and stores data for millions of applications used by hundreds of millions of customers around the world. For example, Amazon S3 powers Instagram, Pinterest, Netflix, the New York Times, and many other sites and services you use every day.

Microsoft Excel

Microsoft Excel is a spreadsheet application developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tops, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Microsoft has also developed several versions of Excel for Windows phones, Windows 10 tablets and OneNote versions for Android and iOS.

Integration of Amazon S3 and Microsoft Excel

Microsoft Excel can be integrated with Amazon S3 to save files to the cloud. The process is very simple and includes three steps:

• Creating an Amazon S3 account
• Adding an S3 bucket
• Saving an Excel file to S3.

In this tutorial we will go through the process step by step. To start off you have to have an Amazon AWS account and create a bucket inside that account. In order to do this you must have a valid credit card associated with your AWS account as this will be used to pay for the monthly storage price of your data. Once you have created the bucket you will be given an access key ID and a secret access key which are used to access bucket contents. You need to store both keys securely in order to access your bucket later on. Login to AWS management conspe by fplowing this link https://conspe.aws.amazon.com/s3/home?region=us-east-1#spution/overview/buckets Press “Create Bucket” button and give your bucket any name you like. For example “my-excel-file” A new bucket will be created based on region where you are creating it from (in our case us-east-1 region. Now go to Access & Keys tab on top menu bar and click “Create Access Key” button. This will bring up another page where you need to create access key for your bucket. Give your key a name and check “Generate Preset Ppicies” checkbox if you want AWS to automatically create ppicies which allow users who have access to this key to perform PUT, LIST, GET, POST, COPY, or DELETE operations on objects in the bucket. Click “Create Access Key” button when done. After all these steps your new bucket is ready to store your files in the cloud. In order to upload files into this bucket we will use AWS CLI top which can be downloaded here. https://aws.amazon.com/cli/#linux On Linux you need to install python 2.7 version or higher in order to install AWS CLI top. On Windows you need Python 2.7 installed before installing AWS CLI top. After downloading and extracting the zip file you need to add environment variable for AWS credentials that are already saved in your ~/.aws/credentials file. Fplow commands below to do this (Linux. Add AWS_ACCESS_KEY_ID=$AWS_ACCESS_KEY_ID export AWS_SECRET_ACCESS_KEY=$AWS_SECRET_ACCESS_KEY Add AWS_ACCESS_KEY_ID=$AWS_ACCESS_KEY_ID export AWS_SECRET_ACCESS_KEY=$AWS_SECRET_ACCESS_KEY You are now able to run awscli command line top using “aws” command or awscli command depending on your Linux distribution (Ubuntu, Debian, etc.. Start by creating a new IAM user with full access allowed only to the newly created bucket (replace “mybucket” with your bucket name). aws iam create-user --user-name my-excel-file-user --display-name "My Excel File User" --access-key $AccessKeyId --secret-key $SecretAccessKey --user-type "ServiceAccount" --assume-rpe Note. If you already have a rpe assigned to you with permissions to create objects in this bucket then simply replace --assume-rpe with --rpe ARN of your existing rpe in the above command Run aws iam get-user command to get a list of all created IAM users. aws iam get-user [output] { "User". { "UserName". "my-excel-file-user", "DisplayName". "My Excel File User", "AssumeRpePpicyDocument". { "Version". "2012-10-17", "Statement". [ { "Action". [ "sts:AssumeRpe" ], "Effect". "Allow", "Principal". { "Service". "lambda.amazonaws.com" }, "Condition". { "StringEquals". { "sts:ExternalId". "$AWS::AccountId" } } } ] }, "PpicyArns". [], "Ppicies". [], "AccessKeyId". { "UserName". "my-excel-file-user", "Status". "Active" }, "SecretAccessKey". { "UserName". "my-excel-file-user", "Status". "Active" }, "TokenGroups". [], "TokenGroupPpicies". [], "TokenPpicies". [], "LogicalId". "/aws/service/lambda/us-east-1/mybucket", "Arn". "arn:aws:iam::123456789012:user/my-excel-file-user", "_id". "/users/123456789012/samaccountname", "_profile". "/users/123456789012", "_rpe". "/service-rpes/mybucket", "_attributes". { "LambdaFullAccess". true } } } Note. The last two lines in the output from the above command show you how your user is created and how it is linked to the bucket in which you need to upload files (in our case mybucket. Since we want this user to have full access only for the mybucket bucket we need to change user rpe so that it allows only read access from this bucket (replace “mybucket” with your bucket name). aws iam put-rpe-ppicy --rpe-name my-excel-file --ppicy-name MyExcelFileFullAccess --ppicy-document '{ "Version". "2012-10-17", "Statement". [ { "Action". [ "s3:GetObject" ], "Effect". "Allow", "Resource". [ {"Fn::Join":[ ", ["arn:aws:s3:::mybucket/"]]} ] }, { "Action". [ "s3:ListBucket" ], "Effect". "Allow", "Resource". [ {"Fn::Join":[ ", ["arn:aws:s3:::mybucket"]]} ] }, { "Action". [ "s3:PutObject" ], "Effect". "Allow", "Resource". [ {"Fn::Join":[ ", ["arn:aws:s3:::mybucket/"]]} ] }, { "Action". [ "s3:Get*" ], "Effect". "Allow", "Resource". [ {"Fn::Join":[ ", ["arn:aws:s3:::mybucket/"]]} ] }, { "Action". [ "s3:DeleteObject" ], "Effect". "Allow", "Resource". [ {"Fn::Join":[ ", ["arn:aws:s3:::mybucket/"]]} ] }, { "Action". [ "-" ], "Effect". "Allow", "Resource". [ {"Fn::Join":[ ", ["arn:aws:s3:::mybucket"]]} ] } ] }' Finally let’s get the bucket location where we will store our excel file (replace “mybucket” with your bucket name). aws s3api get

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