Google Apps Script Spreadsheet API

This Google Apps Script provides a simple API for interacting with Google Sheets, allowing you to create sheets, add columns, append data, and retrieve data.

Features

• Create Sheet: Creates a new Google Sheet with a given title and returns the sheet ID.
• Add Column: Adds a new column to an existing sheet with a specified name.
• Append Data: Appends a new row of data to an existing sheet.
• Get Data: Retrieves all data from an existing sheet.

Usage

Deployment

1. Open Google Apps Script: Open your Google Sheet, and go to "Extensions" > "Apps Script".
2. Copy and Paste the Code: Copy the provided code and paste it into the script editor.
3. Save the Script: Save the script (e.g., "SpreadsheetAPI").
4. Deploy as Web App:
   • Go to "Deploy" > "New deployment".
   • Click the gear icon and select "Web app".
   • Set "Execute as" to "Me".
   • Set "Who has access" to "Anyone with link".
   • Click "Deploy".
   • Copy the "Web app URL" provided after deployment. This URL is your API endpoint.

API Endpoints

• POST (for creating, adding columns, and appending data):
  • URL: Your Web app URL
  • Content-Type: application/json
  • Body: JSON object with the desired action and data.
• GET (for retrieving data):
  • URL: Your Web app URL?sheetId={sheetId}

Request Examples

1. Create Sheet:

{
  "action": "createSheet",
  "title": "My New Sheet"
}

Response:

{
  "sheetId": "YOUR_SHEET_ID"
}

2. Add Column::

{
  "action": "addColumn",
  "sheetId": "YOUR_SHEET_ID",
  "name": "New Column Name"
}

Response:

{
  "status": "Column added"
}

3. Append Data:

{
  "action": "appendData",
  "sheetId": "YOUR_SHEET_ID",
  "data": ["value1", "value2", "value3"]
}

Response:

{
  "status": "Data appended"
}

4. Get Data (using GET request in your browser or with curl):

YOUR_WEB_APP_URL?sheetId=YOUR_SHEET_ID

Response:

[["Header1", "Header2", "Header3"], ["value1", "value2", "value3"], ["value4", "value5", "value6"]]

5. Notes:

1. Replace YOUR_SHEET_ID and YOUR_WEB_APP_URL with your actual sheet ID and web app URL.
2. The sheet created by createSheet is shared with anyone with the link, with edit permissions. Be mindful of the security implications. Error handling is basic. You may want to add more robust error checking for production use.
3. The data retrieved by the doGet function is a 2D array, representing the sheet's rows and columns.
4. The appendData action expects the data array to match the number of columns in the spreadsheet, or it will fill in empty cells.
5. When adding columns, the new column will be added to the rightmost column of the first sheet.