Automating the processing and deployment of testSites.json

[zboldyga]

The test site 'database' is just a JSON file that loads into the website user's browser via AJAX. We are storing this file at: https://storage.googleapis.com/covid19-resources/testSites.json .

This 'database' is stored in a Google Sheet. We would like to keep it in the Google Sheet, for several reasons:

• Managing a database is more time consuming, costs $, could face challenges depending on website scale.
• We are collecting data via a Google Form, which plugs directly into the spreadsheet.

To turn this spreadsheet data into JSON at the above link, we currently do the following steps:

• Manually export a TSV file from Google Sheets
• Use a short Node.js script to parse the file and output it as JSON.
• Manually upload the file to google cloud storage (overwriting old file)
• Manually adjust the cache rules on the file so that the file expires in browser caches every 7200 seconds. NOTE: there are also CORS rules set on the Cloud Storage bucket. These were set when the bucket was created, and do not need to be set when uploads are created.

It would be great to automate this process.

• It may be tricky to get a Cron job to run inside of Google Sheets. We might need to use UIPath or something like this to open the sheet and run the export.
• One approach we considered: create a separate git repository for the testSites.json file, commit and push new site updates, and use Buddy CI/CD to deploy the file to cloud storage. This would require very little coding, and it would provide a nice history of all of the uploads. (easy rollback in the event of errors).

If you want to implement this, just comment below!

[abshomali]

I should be able to implement this. I'm thinking we add a column to the sheet that the reviewers can update with a value indicating whether that entry is approved. I'll write a Python scripts that uses the Sheets API to check that column and retrieve the entries that are approved. There's no need to export and parse the whole thing, so don't expect cron job to be a problem. It would then format that data into a JSON that would get uploaded to GCS with the desired caching configs. Haven't done that last bit but I'm sure I can figure out if I read their docs. I'm a data analyst/scientist not an engineer, so please let me know if I'm overlooking anything.

[zboldyga]

@abshomali Here's the JSON currently hosted at GCS: https://storage.googleapis.com/covid19-resources/testSites.json .

For clarity, here are the JSON fields and their corresponding column numbers (0-indexed):

const currSite = {
        name: col[2],
        address: col[3],
        city: col[4],
        state: col[5],
        zip: col[6],
        lat: parseFloat(col[7]),
        lng: parseFloat(col[8]),
        phone: col[9],
        hours: col[10],
        days: col[11],
        opPeriod: col[12],
        link: col[13],
        appReq: col[14],
        docScreen: col[15],
        driveThru: col[16],
        walkUp: col[17],
        description: col[18],
        estCap: col[19],
        comments: col[20]
    }

Column 0 is the timestamp the entry was inserted. Column 1 is the 'Accepted' column, which will contain '1' if the row is accepted.

If you write a python script that can be run from the command line, I can write a small script to start-up a cron job and I can add to the package.json to deploy this in the CI/CD process.

[zboldyga]

Additionally, there is a bit of data cleanup that would be really handy!

• Data validation would be great. If data for a particular field seems incorrect, you can either skip the row or print to console depending on the issue severity. We will feed sys logs into a monitoring tool.
• Phone numbers are not being formatted consistently.
• There may be other cases where cleanup is helpful, I can't recall off the top of my head.

[zboldyga]

Would you be OK with using your own credentials for GCS to start? I was thinking about this just now; I will have to set up two environments for staging and prod, add some configs in the bootup scripts to separate this, etc. Might take some tinkering on my end, don't want to hold you up!

If that works, then when you are ready to deploy I can plug the credentials into the project and configure the right settings for the staging and prod deployments.

[abshomali]

This is great, thanks for sharing the JSON details. Yup, I'll set up my own GCS bucket and test this out with it. It'll make testing easier too. Will look to do data validation as well. I'll comment here if I have other questions.

[abshomali]

Can you give some context to the step where you Manually adjust the cache rules on the file so that the file expires in browser caches every 7200 seconds.? It seems like it should be a characteristic of each blob but can't find how to set it, so if you have any guesses as to the name of the attribute I need to set, that would be helpful. Or just link me to an article that explains it if you know of one. Thanks!

[zboldyga]

@abshomali Actually, I was just reading about the cache-control metadata tag here:

https://cloud.google.com/storage/docs/metadata?hl=en_US&_ga=2.147992055.-1345538864.1585370861#cache-control

It looks like the default is: public, max-age=3600 , which means we don't need to do anything! The objects will automatically expire in the user's cache every 3600 seconds (1 hour), so we don't have to worry much about users having stale data in their browsers :)