[SOLVED] How get raw cell data from google sheets?

How get raw cell data from google sheets?

I receive data using Google Sheets API and want to get a link to a file that is located on Google Drive, the link is displayed as an icon and label as in the screenshot.

I am using the method https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get but in response I always get “test_file.kml” instead of a link to Google Drive.

How can I get the value of a link?

Solution

Issue and workaround:

In the current stage, unfortunately, it seems that the link of the file of the smart chip cannot be directly retrieved by Sheets API. But, as the current workaround, when the following flow is run, the link of the file of the smart chip can be retrieved by Sheets API.

Convert Google Spreadsheet to XLSX.
Convert XLSX to Google Spreadsheet.

By this flow, the smart chips are converted to the values with the links. This idea is from this post (Author: me). In this case, the link can be retrieved by Sheets API. Of course, you can also directly retrieve the link from the XLSX data converted from Google Spreadsheet. In that case, Sheets API cannot be used.

Sample curl command

When the above flow is achieved by curl comments, it becomes as follows.

1. Convert Google Spreadsheet to XLSX

In this case, Google Spreadsheet is exported as an XLSX file by Drive API.

curl \
  'https://www.googleapis.com/drive/v3/files/[fileId]/export?mimeType=application%2Fvnd.openxmlformats-officedocument.spreadsheetml.sheet' \
  -H 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  -o sample.xlsx

2. Convert XLSX to Google Spreadsheet

In this case, the XLSX file is uploaded by converting to Google Spreadsheet by Drive API.

curl \
  'https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart' \
  -H 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  -F 'metadata={"name":"sample.xlsx","mimeType":"application/vnd.google-apps.spreadsheet"};type=application/json' \
  -F 'file=@sample.xlsx'

Here, please check the file ID of the uploaded file from the returned value. This file ID is used with the Sheets API of the next step.

3. Retrieve the link of the smart chip

In this case, the link of the smart chip is retrieved from the Google Spreadsheet converted from an XLSX file using Sheets API. Please set the file ID to [fileId]. And, please set the cell coordinate you want as the A1Notation. If the link of the smart chip is 'Sheet1'!A1, please add ranges=%27Sheet1%27!A1 to the query parameter.

curl \
  'https://sheets.googleapis.com/v4/spreadsheets/[fileId]?ranges=%27Sheet1%27!A1&fields=sheets(data(rowData(values(hyperlink))))' \
  -H 'Authorization: Bearer [YOUR_ACCESS_TOKEN]'

By this, when the smart chip of the file link exists in 'Sheet1'!A1, the following result is returned.

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "hyperlink": "###"
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

Note:

This answer explains the workaround for achieving your goal. If you actually use this workaround, please create a script for your situation.
In this answer, it supposes that the access token is valid token for using Drive API and Sheets API. Please be careful about this.