Custom GPTs OAuth Settings and API for Google Sheets

Conversation Settings

Converstations Starter: Create a new project: Type: Project, Assignee: John, Category: Job

Instructions (appended to your exsting instructions): When a project data is provided, please update Google Sheets ID: `<sheet id>` using `sheets.spreadsheets.values.append` api call with the information provided and starting at range: `Sheet1!A2`, and continue adding rows as needed.




NOTE: How to find the Google Sheet ID - https://stackoverflow.com/questions/36061433/how-do-i-locate-a-google-spreadsheet-id

Credentials Settings (console.google)

Authorized JavaScript origins: https://chat.openai.com
Authorized redirect URIs: `provided by ChatGPT in the configure settings after creating an action. Format: https://chat.openai.com/aip/{gpt id}/oauth/callback`


NOTE: This is where you'll get the `Client ID` and `Client secret`

Google Sheets Schema

openapi: 3.0.3
info:
  title: Google Sheets API Update Operation
  description: An OpenAPI specification for updating a range of cells in a Google Sheet.
  version: "1.0"
servers:
  - url: 'https://sheets.googleapis.com/v4/spreadsheets'
paths:
  /{spreadsheetId}/values/{range}:
    put:
      summary: Update a range of values in a spreadsheet
      operationId: updateSpreadsheetValues
      parameters:
        - name: spreadsheetId
          in: path
          required: true
          schema:
            type: string
          description: The unique identifier for the spreadsheet.
        - name: range
          in: path
          required: true
          schema:
            type: string
          description: The A1 notation of the range to update.
        - name: valueInputOption
          in: query
          required: true
          schema:
            type: string
            enum:
              - RAW
              - USER_ENTERED
          description: How the input data should be interpreted.
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/ValueRange'
      responses:
        '200':
          description: Success
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/UpdateValuesResponse'
        '400':
          description: Bad Request
components:
  schemas:
    ValueRange:
      type: object
      properties:
        values:
          type: array
          items:
            type: array
            items:
              type: string
          description: The new data to be placed in the range.
    UpdateValuesResponse:
      type: object
      properties:
        spreadsheetId:
          type: string
        updatedRange:
          type: string
        updatedRows:
          type: integer
        updatedColumns:
          type: integer
        updatedCells:
          type: integer

GPT Authentication Settings

Authentication Type: OAuth
Client ID: `your client id found @ https://console.cloud.google.com/apis/credentials`
Client Secret: `your client secret @ https://console.cloud.google.com/apis/credentials`
Authorization URL: https://accounts.google.com/o/oauth2/auth
Token URL: https://oauth2.googleapis.com/token
Scope: https://www.googleapis.com/auth/spreadsheets
Token Exchange Method: Basic authorization header

OAuth Settings

Navigate to: `https://console.cloud.google.com/apis/credentials/consent` and create an `OAuth consent screen`


Page 1:
App Name: `<Give it a name>`
User support email: `<your email address>`
Publishing status: Test
User type: External
Test users: `<your email address>`

Application home page: https://chat.openai.com/
Application privacy policy link: https://chat.openai.com/
Authorized domain 1: openai.com

Developer contact information: `<your email address>`

Page 2:
Scopes (search for `Sheets`): 
.../auth/spreadsheets
.../auth/spreadsheets.readonly

Page 3:
Test users: `<your email address>`


"Save and continue"