US flag signifying that this is a United States Federal Government website An official website of the United States Government

Create a Google Script to convert Google Sheet data into JSON

Closed
Ended on 03/14/2016
Winning bid (Dan Siddoway): $249.00

Skills

Google Apps Script and JSON

The Federal Risk and Authorization Management Program, or FedRAMP, is a government-wide program that provides a standardized approach to security assessment, authorization, and continuous monitoring for cloud products and services. This issue seeks a Google Apps Script that will convert a Google Sheet into JSON which adheres to a given schema file. This script will allow future front-end applications to use the FedRamp data.

We use a non-public Google Sheet. Because we cannot make this sheet fully public, we have made available a CSV which has the same fields as the Google Sheet. However, the winner of the auction will be given access to the Google Sheet. It's worth noting that this Google Sheet contains only fake data (and no sensitive data).

Once given access to the Google Sheet, the winner of the auction can begin writing the script. Once the JSON is created by the Google script, the script should post the resulting file onto Github using the Github API.

The mapping file indicates how fields from the Google Sheet should be mapped to fields in the JSON. The schema file indicates how packages should be aggregated based on Package ID and displayed in the resulting JSON file produced by the Google Script.

Auction rules

Registered users on micropurchase.18f.gov may bid to deliver the requirements in this auction. The lowest bidder at the time the auction closes shall receive the award. The awarded bidder shall have five business days after notice of award to deliver the requirements. Upon successful completion of the requirements, 18F shall pay the winning bidder.

Also see our policies and guidelines page.

Acceptance criteria

Creating the Google Script

Given the P-ATO CSV of the data, the mapping, and the schema JSON files which are available in the repository,

When the Google Script runs,

Then the script will produce a JSON file of the data in a Google Sheet in format delineated by the schema.json

And the name of the resulting file will follow the naming scheme: MM-DD-YYYY.json


Publishing the data to GitHub

Given GitHub API credentials,

And the Google Script (defined above),

When the script produces the JSON in the schema provided,

Then the script will publish the resulting JSON to the data directory of the GitHub repository via the GitHub API.

General Acceptance Criteria

  • B or better Code Climate grade and 90% or higher test coverage for all modifications.
  • Tests run on Travis-CI and do not break the build

Resources

Deliverables

A single pull request submitted to https://www.github.com/18F/fedramp-micropurchase (inside the scripts folder) with clear, descriptive commits that satisfy all of the above acceptance criteria.

Bidder DUNS Amount Date (EST)
Daniel Connery 079859219 $3,000.00 March 14, 2016 12:10:36 PM EDT
Dan Siddoway 080014807 $249.00 * March 14, 2016 03:05:19 AM EDT
Island Peak Software 078327018 $1,200.00 March 11, 2016 11:07:34 AM EST
Kevin Fan 079150065 $3,421.00 March 10, 2016 07:50:50 PM EST
Polymorphic Engineering Solutions, Inc. 080034592 $2,850.00 March 10, 2016 07:24:25 PM EST

* - winning bid

Bidding closed

Bidding for this auction closed on March 14, 2016 02:00:00 PM EDT.

Auction started at

March 10, 2016 01:00:00 PM EST

Auction ended at

March 14, 2016 02:00:00 PM EDT

Delivery deadline

March 21, 2016 02:00:00 PM EDT

Eligible vendors

SAM.gov only

Auction type:

Sealed bid (rules)

Paid at:

April 01, 2016 10:44:28 AM EDT

Accepted at:

March 21, 2016 02:00:00 PM EDT

View on GitHub