Sheetsu Clone

1. Getting started

1. Accept the HW5 assignment

• Follow this link, where HW5 is stored on GitHub Classroom.
• Accept the homework and download the starter code as you did in Homework 0.

2. Install Node v10

• Follow the Node Installation instructions.
• Make sure you have the right version of node by running node -v in your terminal. You should see v7.10:

2. SETUP: Create a Google Service Account

In order to have our NodeJS server write to a Google Spreadsheet, the server must have permission to write to the spreadsheet.

Google allows two ways of doing this:

1. OAuth 2.0: This allows a user to log in and manage spreadsheets owned by the user. This is not what we want; we want to be able to write to a single spreadsheet that we own.
2. Server-to-Server: This allows our NodeJS server to talk to Google’s servers directly, e.g. to write to a spreadsheet. This is what we want to do.

To have our NodeJS server write to a Google Spreadsheet, we need to set up two things:

• A Google Service Account (GSA)
• A Google Service Account key

This section contains the instructions for obtaining both a Google Service Account and a GSA key, and how to move the key to the HW5 starter code.

0. Log into your @gmail account

• Sign into your Gmail account.
  • Your @stanford account will not work for this part, even though you can access Gmail through it. You must use an @gmail.com account. (see piazza post)
  • Create a Gmail account for free if you need to.

1. Add the Google Sheets API

• Navigate to https://console.developers.google.com.
• In the API search box, type “Google Sheets API”. When it comes up, click on “Google Sheets API.”

2. Create a project

• You will now need to create a project. Click the “Create Project” button.
• You may get a dialog message that looks like the one below. If you do, click “Create”.
• Enter any project name, such as “4102165 HW5.”
• You will see an intermediate loading screen, then it will take you back to the Google Sheets API page.

3. Enable the API

• Click the “Enable” button. You will be taken to the Credentials page.

4. Create Google Service Account + key

• Click the “Create Credentials” button. This will take you to the Credentials wizard.
• Fill out the first part of the wizard as follows:
  • Which API are you using? Google Sheets API
  • Where will you be calling the API from? Web server
  • What data will you be accessing? Application data
  • Are you using Google App Engine or Google Compute Engine? No, I’m not using them
• Then click “What credentials do I need?” This will prompt you to create a Google Service Account.
• Fill out “Create a service account” second part of the form as follows:
  • Service account name: Any name you’d like, e.g. “HW5 sheets”
  • Role: Project -> Owner
  • Key type: JSON (this is selected by default)
• Then click “Continue.”
• This will immediately download your Google Service Account credentials JSON file for you.
  • Make note of where this file was downloaded! We will refer to this file again soon.

5. Add Google Drive API

• After you’ve created a Google Service Account, you will be taken back to the Credentials screen. From this screen, click “Library” on the left sidebar.
• Search for “google drive api,” then click on “Google Drive API” when it comes up.
• Click “Enable”, like you did for the Google Sheets API

We are now done with the Google Developers Console.

6. Copy GSA credentials to privateSettings.json

• In Atom, open the credentials JSON file that was downloaded for you at the end of Step 4. It should look something like this:
  • The most important entries are the private_key and client_email fields.
• In Atom, also open your HW5 starter code and the privateSettings.json file. privateSettings.json should be completely empty.
• Now copy the contents of the credentials JSON file into privateSettings.json.

3. SETUP: Create a GSA spreadsheet

In this section, you will create a special Google spreadsheet to which your NodeJS server will be able to write data. To do this, you will need to create a spreadsheet via the hw5-tools command-line tool, distributed on npm.

1. Install hw5-tools

• Goto https://github.com/fullstackccu/hw5-tools.git and following the install instruction.

2. Run hw5-tools

• Now run the hw5-tools command:
• Type “c” to create a new spreadsheet and enter the following:
  • Title of spreadsheet: Anything you’d like, e.g. “HW5 spreadsheet”
  • Enter your email: Your email address (must be a Gmail-enabled account, such as @gmail or @stanford)
• Afterward the tool will print out “Spreadsheet created” with the URL of your new spreadsheet.
  • The only people who can access this spreadsheet are you (via the email you entered) and the course staff.

NOTES:

Technically the spreadsheet you just created is owned by your Google Service Account, and not your public Gmail account, which is why you need to use the command-line tool to create and manage the spreadsheet. You cannot access Google Service Account documents via Google’s web interface.

3. Open your spreadsheet and fill with some fake data

• Open the spreadsheet you just created via command-line
• Fill it out with fake data like in this spreadsheet
  • The first row of your spreadsheet should be the column labels.

4. SETUP: Set up the starter code

At last, you are ready to configure the starter code and get working on the assignment!

Note: In this assignment, you will not need to modify any of the code in public/, including public/js/script.js.

1. Set SPREADSHEET_ID in server.js

• Open server.js from the HW5 starter code in Atom
• Change the value of SPREADSHEET_ID to the ID of your spreadsheet. This is the long series of numbers, letters, and dashes after the d/ in the Google Spreadsheets URL.
  • For example, in https://docs.google.com/spreadsheets/d/1GYI-P9g2-7g9PtF8ygZ8LNV8FrGaTpz9f3G-eVTj370, the Spreadsheet ID is 1GYI-P9g2-7g9PtF8ygZ8LNV8FrGaTpz9f3G-eVTj370

2. Run npm install

• In the terminal, make sure you are still in the directory that contains the HW5 starter code you downloaded from GitHub.
• Run npm install

3. Run npm start

• Now run npm start to start your server. You should see the “Server listening on port 3000!” message.

4. Test http://localhost:3000

• Navigate to http://localhost:3000

• You should see a fully-implemented test page that you will use to test your backend.

• On the test page, click the “FETCH” button.
• After a few seconds, you should see the following at the bottom of the page:
• On the terminal where your server is running, you should see what the output of console.log(rows):

5. Change and reload server

Every time you change the code in server.js, you will have to kill and rerun your server process. Let’s practice that here to make sure you understand your development workflow.

• Modify server.js in trivial ways:
  • Have it print a slightly different message after the server process is bound to port 3000.
  • Have it return { status: 'unimplemented!!!'} in response to a GET request.
• Return to the terminal and stop the current node server by pressing CTRL-C
• Now rerun npm start
  • Verify the terminal prints you updated message
• Navigate to http://localhost:3000 and click “Fetch”
  • Verify the result returned is updated.

Using the gsa-sheets node module

To update the spreadsheet, you will be using the gsa-sheets Node library.

The starter code already creates a Sheet object for you:

const sheet = googleSheets(key.client_email, key.private_key, SPREADSHEET_ID);

And the onGet() function calls and prints the results of the sheet.getRows() method:

const result = await sheet.getRows();
const rows = result.rows;
console.log(rows);

Here are the public methods of a Sheet object:

Notes: Spreadsheet Columns

In the examples below, we use a spreadsheet with an “email” and a “name” column.

However, you cannot assume the spreadsheet has an “email” and a “name”; you must make it work with whatever column names are specified in the spreadsheet.

• You should always treat the first row of the spreadsheet as the names of the columns in the spreadsheet.
• You may assume that every spreadsheet will have at least one column specified, i.e. you do not have to support an empty spreadsheet

Here’s an example of another valid spreadsheet:

Milestone 1: Implement GET

When you send a GET request to http://localhost:3000/api, the server should reply with a JSON representation of the spreadsheet as described below:

• Return an array of objects, where each object represents a row:
  • Each object should be key-value pairs of the form column: value

For example, if your spreadsheet that looks like this:

Then a GET request to http://localhost:3000/api should return JSON that looks like:

[
  {
    "name": "Bert",
    "email": "bert@sesame.street"
  },
  {
    "name": "Ernie",
    "email": "ernie@sesame.street"
  },
  {
    "name": "Big bird",
    "email": "bigbird@sesame.street"
  }
]

Spreadsheet Format:

• You may assume the spreadsheet is formatted perfectly:
  • The first row of the spreadsheet contains the column names.
  • Every row with a value in it is filled out completely, i.e. there are no partially filled out rows. (screenshot)
  • All values belong to a named column (screenshot)
  • There are no blank rows in between filled out rows. That is, it’s not possible for row 5 and row 7 to be filled out, but row 6 to be blank. (screenshot)

Testing GET:

Use the test page located at http://localhost:3000/ to verify your result. Here’s a screenshot of what the test page looked like after clicking the “Fetch” button:

Milestone 2: Implement DELETE

When you send a DELETE to http://localhost:3000/api/ with route parameters column-name/value your server will delete the first row whose column value matches the given parameter.

• In other words, a DELETE request to http://localhost:3000/api/name/Anderson would delete the first row whose “name” value was “Anderson.”

DELETE request details:

• If there are multiple rows that match the given parameter, delete the first matching row.
• If there are no rows whose value matches the given parameter, you should still return a success response but the spreadsheet should not change.
• For simplicity, you do not have to support case insensitivity; that is extra credit. In other words, if your column name is “email”, you do not have to make your DELETE request work with api/Email/o@thegrouch.net. Similarly, if a row value for “name” is “Anderson”, you do not have to delete that row if the DELETE request is for api/name/anderson

DELETE response:

Your server should respond with a success message after updating the spreadsheet, or when your server determines spreadsheet should not be updated.

{
  "response": "success"
}

Testing DELETE:

This example assumes your spreadsheet looks like this:

Use the test page located at http://localhost:3000/ to verify your result:

• Set the API path to /api/name/Ernie
• Set the “Method” to DELETE

Here’s a screenshot of what the test page looked like after clicking the “Fetch” button:

And here’s a screenshot of the updated spreadsheet:

Milestone 3: Implement POST

When you send a POST request to http://localhost:3000/api with a message body in the format described below, your server should append a row to the end of the spreadsheet.

POST request details:

• The message body will be a JavaScript object that represents the row to add to the spreadsheet.
• The JavaScript object contains key-value pairs of the form column: value
• For example, the message body might look like:

  {
    "name": "Oscar",
    "email": "o@thegrouch.net"
  }
  

• The column-value pairs will not necessarily be defined in the same order as the columns in the spreadsheet. For example, another valid message body is the following:

  {
    "email": "me@el.mo",
    "name": "Elmo"
  }
  

• You may, however, assume that the message body is perfectly formatted, i.e. the value for each column is filled out, and it does not specify any columns that don’t already exist in the spreadsheet.
• For simplicity, you do not have to support case insensitivity; that is extra credit. In other words, if your column names are “name” and “email”, you do not have to make your POST request work with { "Name": "Elmo", "Email": "me@el.mo" }

POST response:

Your server should respond with a success message after updating the spreadsheet.

{
  "response": "success"
}

Testing POST:

This example assumes your spreadsheet looks like this:

Use the test page located at http://localhost:3000/ to verify your result:

• Make sure the API path is /api
• Set the “Method” to POST
• Click “Add” next to “Body data” twice and fill out these keys and values:
  • name : Oscar
  • email : o@thegrouch.net

Here’s a screenshot of what the test page looked like after clicking the “Fetch” button:

And here’s a screenshot of the updated spreadsheet:

EXTRA CREDIT: Implement PATCH

When you send a PATCH request to http://localhost:3000/api with route parameters column-name/value and a message body in the format described below, your server will update the first row whose column value matches the given parameter, with the values as described in the message body.

PATCH request details:

• The message body will be a JavaScript object that represents the column values to update for the row.
• The JavaScript object contains key-value pairs of the form column: value
• For example, you might have a PATCH request http://localhost:3000/api/name/Oscar with a message body that looks like:

  {
    "email": "me@osc.ar"
  }
  

• The JavaScript object will not necessarily contain every column in the spreadsheet.
• You may assume that the message body keys are valid, i.e. the object only refers to columns that exist in the spreadsheet.
• If there are multiple rows that match the given parameter, update the first matching row.
• If there are no rows whose value matches the given parameter, you should still return a success response but the spreadsheet should not change.
• For simplicity, you do not have to support case insensitivity; that is extra credit.

PATCH response:

Your server should respond with a success message after updating the spreadsheet.

{
  "response": "success"
}

Testing PATCH:

This example assumes your spreadsheet looks like this:

Use the test page located at http://localhost:3000/ to verify your result:

• Set the API path to /api/name/Ernie
• Set the “Method” to PATCH
• Click “Add” next to “Body data” twice and fill out these keys and values:
  • email : me@ernie.com

Here’s a screenshot of what the test page looked like after clicking the “Fetch” button:

And here’s a screenshot of the updated spreadsheet:

EXTRA CREDIT: Case-insensitive search

For extra credit, make your server support case-insensitive search for:

• DELETE
• PATCH
• POST

The case insensitivity should be on the column name.