Skip to main content
Version: 7.0.0

Update Googlesheets

One of our Actions, update.spreadsheet.row allows you to reflect input information in Google Sheets and save completed document information.
To proceed with this scenario, you need to follow these steps:

steps
  1. Obtain the OAuth URL and complete the OAuth process.
  2. Retrieve the values of the spreadsheet to be updated.
  3. Retrieve the worksheet values of the spreadsheet to be updated.
  4. Retrieve the row values of the spreadsheet to be updated.
  5. Update the spreadsheet using the Action update.spreadsheet.row

1. Google Spreadsheet OAuth​

Please complete the 1. Obtain OAuth URL step.
By following the instructions below, you will receive the OAuth URL.

[Request]

curl --location 'http://localhost:1290/v1/service/googlesheets/oauth?userId={{yourUserId}}' \
--header 'api_key: default'

[Response]

https://accounts.google.com/o/oauth2/v2/auth?prompt=consent&access_type=offline&include_granted_scopes=true&response_type=code&redirect_uri=https%3A%2F%2Fproxy.interactor.com%3A4443%2Fplatform%2FUNLICENSED%2Foauth&state=XCP.jp_lM3LyKyQK8EabFJYThEvVCTPNZ4j9CWRkjfGnk6ha0GEJyluMXXulJLsl0y71v_lPRK9peDN_PIEcq6dodL70tjOQ67wiRTVfLIos6HSiiC8&client_id=859731562219-6vjoh4d3ovfpihfs0hrti7s5f651c1l7.apps.googleusercontent.com&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fuserinfo.email+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive.readonly+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive.file+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets

2. Retrieve the values of the spreadsheet to be updated​

Execute the step 2. Retrieve the values of the spreadsheet to be updated to obtain the spreadsheet values that require updating.
The Request Body parameter is optional.
If it is not provided, values will be retrieved from the default location, My Drive, in Google Drive.

2-1 Example - Retrieve values from My Drive​

[Request]

curl --location 'http://localhost:1290/v1/service/googlesheets/datatype/GoogleSpreadsheet/execute?userId={{yourUserId}}' \
--header 'api_key: default' \
--header 'Content-Type: application/json' \
--data '{
}'

[Response]
The values are retrieved from My Drive.

{
  "input": {},
  "output": {
    "data": [
      {
        "label": "Test Sheet",
        "value": {
          "id": "1oFClVS4x12312zcTDL5IeJrg9GaasdqavY",  // {{SheetId}}
          "name": "Test Sheet"
        }
      },
      ...
    ]
  }
}

2-2 Example - Retrieve values from a specific drive (Optional)​

To retrieve values from a specific drive, include the following values in the Request Body.
These values can be obtained using the API "(Optional) Get GoogleDrive" in last step.

Get GoogleDrive ID​

[Request]

curl --location --request POST 'http://localhost:1290/v1/service/googlesheets/datatype/GoogleDrive/execute?userId={{yourUserId}}' \
--header 'api_key: default' \
--data ''

[Response]

{
  "input": {},
  "output": {
    "data": [
      {
        "label": "My Drive",
        "value": {
          "id": "0AM1z9G9z1z9G9Uk9PVA",  // {{drive_id}} using this value for the next step
          "name": "My Drive"
        }
      },
      ...
    ]
  }
}

Retrieve values from a specific drive's spreadsheets​

[Request]

curl --location 'http://localhost:1290/v1/service/googlesheets/datatype/GoogleSpreadsheet/execute?userId={{yourUserId}}' \
--header 'api_key: default' \
--header 'Content-Type: application/json' \
--data '{
  "drive": {
    "id": {{drive_id}}
  }
}'

[Response]
The values are retrieved from the drive specified in the Request Body.

{
  "input": {
    "drive": {
      "id": "{{drive_id}}"
    }
  },
  "output": {
    "data": [
      {
        "label": "Test Sheet",
        "value": {
          "id": "1oFClVS4x12312zcTDL5IeJrg9GaasdqavY",  // {{SheetId}}
          "name": "Test Sheet"
        }
      },
      ...
    ]
  }
}

3. Retrieve the worksheet values of the spreadsheet to be updated​

Using the spreadsheet ID obtained in step 2, retrieve the worksheet values.
Perform the 3. Retrieve the worksheet values of the spreadsheet to be updated step.

[Request]

curl --location 'http://localhost:1290/v1/service/googlesheets/datatype/GoogleSheetWorksheet/execute?userId={{yourUserId}}' \
--header 'api_key: default' \
--header 'Content-Type: application/json' \
--data '{
  "spreadsheet" : {
    "id": "1uFcsyrlQixAc2hMhG2OojiIg1Mth9TQYYSaGi88P28U"  // Enter one of the spreadsheet values obtained in step 2.
  }
}'

[Response]

{
  "input": {
    "spreadsheet": {
      "id": "1oFClVS4x12312zcTDL5IeJrg9GaasdqavY"
    }
  },
  "output": {
    "data": [
      {
        "label": "Sheet1",
        "value": {
          "gridProperties": {
            "columnCount": 39,
            "frozenColumnCount": 2,
            "frozenRowCount": 2,
            "rowCount": 1053
          },
          "id": 0,
          "index": 0,
          "sheetId": 0,
          "sheetType": "GRID",
          "title": "Sheet1"  // {{WorkSheetTitle}}
        }
      }
      ...
    ]
  }
}

4. Retrieve the row values of the spreadsheet to be updated​

Using the worksheet ID obtained in step 3, retrieve the existing row values.
Perform the 4. Retrieve the row values of the spreadsheet to be updated step.

[Request]

curl --location 'http://localhost:1290/v1/service/googlesheets/datatype/GoogleSheetWorksheetRow/execute?userId={{yourUserId}}' \
--header 'api_key: default' \
--header 'Content-Type: application/json' \
--data '{
  "spreadsheet": {
    "id": "{{SheetId}}"  // Enter one of the spreadsheet values obtained in step 2.
  },
  "worksheet": {
    "title": "{{WorkSheetTitle}}"  // Enter one of the worksheet values obtained in step 3.
  }
}'

[Response]

{
  "input": {
    "spreadsheet": {
      "id": "1uFcsyrlQixAc2hMhG2OojiIg1Mth9TQYYSaGi88P28U"
    },
    "worksheet": {
      "title": "Sheet1"
    }
  },
  "output": {
    "data": [
      {
        "label": "header",
        "value": {
          "isHeader": true,
          "range": "A1:B1",
          "values": [
            "Fruit",
            "Price"
          ]
        }
      },
      {
        "label": "Kiwi, 10 Won",
        "value": {
          "isHeader": false,
          "range": "A2:B2",
          "values": [
            "Kiwi",
            "10 Won"
          ]
        }
      },
      {
        "label": "Banana, 100 Won",
        "value": {
          "isHeader": false,
          "range": "A3:B3",
          "values": [
            "Banana",
            "100 Won"
          ]
        }
      },
      ...
    ]
  }
}

5. Update the spreadsheet using the Action update.spreadsheet.row​

Finally, use the values obtained in step 4 to update the spreadsheet using the Action "update.spreadsheet.row."
Enter the desired range and values in the row field.
To update existing row values, enter the range of the corresponding row and the values you wish to update.
To add a new row, specify the range of the new row and the values you wish to add.

[Request]

curl --location 'http://localhost:1290/v1/service/googlesheets/action/update.spreadsheet.row/execute?userId={{yourUserId}}' \
--header 'api_key: default' \
--header 'Content-Type: application/json' \
--data '{
  "worksheet": {
    "title": "{{WorkSheetTitle}}"  // Enter one of the worksheet values obtained in step 3.
  },
  "spreadsheet": {
    "id": "{{SheetId}}"  // Enter one of the spreadsheet values obtained in step 2.
  },
  "row" :{ 
    "range": "A3:B3", 
    "values": [
      ["kiwi", "2024-08-28-5:03:42 PM"]
    ]
  }
}'

[Response]

{
  "input": {
    "row": {
      "range": "A3:B3",
      "values": [
        [
          "kiwi",
          "2024-08-28-5:03:42 PM"
        ]
      ]
    },
    "spreadsheet": {
      "id": "1uFcsyrlQixAc2hMhG2OojiIg1Mth9TQYYSaGi88P28U"
    },
    "worksheet": {
      "title": "Sheet1"
    }
  },
  "output": {
    "data": {
      "spreadsheetId": "1uFcsyrlQixAc2hMhG2OojiIg1Mth9TQYYSaGi88P28U",
      "updatedCells": 2,
      "updatedColumns": 2,
      "updatedRange": "'Sheet1'!A3:B3",
      "updatedRows": 1
    }
  }
}

The result will indicate that the update was successful, along with the information of the updated row.

If you have any questions, please feel free to contact us anytime.