CMS-Sheets

---

It's a simple API RESTful to manipulate a google spreadsheet document.
In order use it, it's necessary to have somethings:

• A google service account and your credentials;
• A google spreadsheet document shared with your google service accout and it ID, got it from URL

🚀 Technologies

This project was developed with the following technologies:

• NodeJS
• Typescript
• Typeorm
• Google-spreadsheet
• VS Code

📋 Features

Documentation

• Lists all spreadsheets in the document
• Lists one spreadsheets in the document
• Add one row in the spreadsheet
• Update one row in the spreadsheet
• Delete one row in the spreadsheet

ℹ️ How To Use

To clone and run this application, you'll need Git, Node.js v12.20.0 or higher + Yarn 1.22.5 or higher installed on your computer. From your command line:

# Clone this repository
$ git clone https://github.com/isaac-allef/cms-sheets.git

# Go into the repository
$ cd cms-sheets

# Install dependencies and run it
$ yarn install
$ yarn dev:server

Routes

method	url	description
get	/sheets/:docId	Lists all spreadsheets in the document
get	/sheets/:docId/:index	Lists one spreadsheets in the document
post	/sheets/:docId/:index	Add one row in the spreadsheet
patch	/sheets/:docId/:index	Update one row in the spreadsheet
delete	/sheets/:docId/:index	Delete one row in the spreadsheet

All routes need this headers:

{
    "client_email": "[google service account email]",
    "private_key": "[private key of the google service account]"
}

Example:

{
    "client_email": "someGoogleServiceAccount@gserviceaccount.com",
    "private_key": "-----BEGIN PRIVATE KEY-----\nRANDOM_STRING\n-----END PRIVATE KEY-----\n"
}

List

Lists all spreadsheets in the document.

URL : /sheets/:docId

Method : GET

URL params: :docId -> [ID obtained from the google spreadsheet URL]

URL params example: /sheets/3yFTzSjpJlpyeLywd1ygY5MiG853f4PBnfaMtrZ63P

Success Response

Code : 200 OK

Content example:

{  
  "sheets": [
    {
      "index": 0,
      "data": [
        {
          "name": "Isaac",
          "age": "25",
          "birth day": "25/12/1995"
        },
        {
          "name": "lavinia",
          "age": "20",
          "birth day": "23/08/2000"
        }
      ]
    },
    {
      "index": 1,
      "data": [
        {
          "fruit": "banana",
          "color": "yellow"
        },
        {
          "fruit": "strawberry",
          "color": "red"
        }
      ]
    }
  ]   
}

Find

Lists one spreadsheets in the document.

URL : /sheets/:docId/:index

Method : GET

URL params:

:docId -> [ID obtained from the google spreadsheet URL]
:index -> [spreadsheet index]

URL params example: /sheets/3yFTzSjpJlpyeLywd1ygY5MiG853f4PBnfaMtrZ63P/0

Success Response

Code : 200 OK

Content example:

{  
  "sheet": {
    "index": 0,
    "data": [
      {
        "name": "Isaac",
        "age": "25",
        "birth day": "25/12/1995"
      },
      {
        "name": "lavinia",
        "age": "20",
        "birth day": "23/08/2000"
      }
    ]
  }
}

Add

Add one row in the spreadsheet.

URL : /sheets/:docId/:index

Method : POST

URL params:

:docId -> [ID obtained from the google spreadsheet URL]
:index -> [spreadsheet index]

URL params example: /sheets/3yFTzSjpJlpyeLywd1ygY5MiG853f4PBnfaMtrZ63P/0

Data params:

{
    "rowValues": [
    {"[column name]": "[value]", "[column name]": [value]},
    {"[column name]": "[value]", "[column name]": [value]}
  ]
}

Data params example:

{
    "rowValues": [
    {"name": "Dilma", "age": 53},
    {"name": "Pedro", "age": 56}
  ]
}

Success Response

Code : 200 OK

Content example:

{  
  "rows": [
    {
      "name": "dilma",
      "age": "53"
    },
    {
      "name": "pedro",
      "age": "56"
    }
  ]
}

Alter

Update one row in the spreadsheet.

URL : /sheets/:docId/:index

Method : PATCH

URL params:

:docId -> [ID obtained from the google spreadsheet URL]
:index -> [spreadsheet index]

URL params example: /sheets/3yFTzSjpJlpyeLywd1ygY5MiG853f4PBnfaMtrZ63P/0

Data params:

{
    "rowIndex": [row index],
    "columnsValues": [
      {"column": "[column name]", "value": "[new value]"},
      {"column": "[column name]", "value": "[new value]"}
    ]
}

Data params example:

{
    "rowIndex": 2,
    "columnsValues": [
      {"column": "name", "value": "Isaac Allef"},
      {"column": "age", "value": "26"}
    ]
}

Success Response

Code : 200 OK

Content example:

{  
  "rows": {
    "name": "Isaac Allef",
    "age": "26",
    "birth day": "25/12/1995"
  }
}

Drop

Delete one row in the spreadsheet.

URL : /sheets/:docId/:index

Method : PATCH

URL params:

:docId -> [ID obtained from the google spreadsheet URL]
:index -> [spreadsheet index]

URL params example: /sheets/3yFTzSjpJlpyeLywd1ygY5MiG853f4PBnfaMtrZ63P/0

Data params:

{
    "rowIndex": [row index]
}

Data params example:

{
    "rowIndex": 2
}

Success Response

Code : 200 OK

Content example:

{  
  "row": {
    "name": "Isaac",
    "age": "25",
    "birth day": "25/12/1995",
    "_deleted": true
  }
}

📝 License

This project is under the MIT license. See the LICENSE for more information.

---

Made with ♥ by Isaac Allef 👋