Is it possible to read google sheets *metadata* only with API key?

Question

It is possible to read data from a sheet only with API key (without OAuth 2.0), but it seems that reading the developer metadata requires OAuth 2.0. Is there some way to read the metadata from an app without asking the user to connect his google account?

Answer 1

• You want to retrieve the developer metadata of the Spreadsheet using the API key.
• You have already been able to get values from Spreadsheet using the API key.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Issue and workaround:

Unfortunately, "REST Resource: spreadsheets.developerMetadata" in Sheets API cannot be used with the API key. In this case, OAuth2 is required as mentioned in your question. The developer metadata can be also retrieved by the method of spreadsheets.get in Sheets API. The developer metadata can be retrieved by the API key. And in this method, all developer metadata is retrieved. So when you want to search the developer metadata, please search it from the retrieved all developer metadata.

IMPORTANT POINTS:

• In this case, please set the visibility of developer metadata to DOCUMENT. By this, the developer metadata can be retrieved by the API key. If the visibility is PROJECT, it cannot be retrieved with the API key. Please be careful this.

• When you want to retrieve the developer metadata with the API key, please publicly share the Spreadsheet. By this, it can be retrieved with the API key. Please be careful this.

Sample situation 1:

As a sample situation, it supposes that it creates new Spreadsheet, and create new developer metadata to the Spreadsheet as the key of "sampleKey" and value of "sampleValue".

In this case, the sample request body of spreadsheets.batchUpdate is as follows.

{
  "requests": [
    {
      "createDeveloperMetadata": {
        "developerMetadata": {
          "location": {
            "spreadsheet": true
          },
          "metadataKey": "sampleKey",
          "metadataValue": "sampleValue",
          "visibility": "DOCUMENT"
        }
      }
    }
  ]
}

Sample curl command:

When you retrieve the developer metadata from above sample Spreadsheet, please use the following curl command.

curl "https://sheets.googleapis.com/v4/spreadsheets/### spreadsheetId ###?key=### your API key ###&fields=developerMetadata"

• In this case, fields=developerMetadata is used to make it easier to see the response value. Of course, you can also use * as fields.
• In this case, when above endpoint is put to the browser, you can see the retrieved value, because of GET method.

Result:

{
  "developerMetadata": [
    {
      "metadataId": 123456789,
      "metadataKey": "sampleKey",
      "metadataValue": "sampleValue",
      "location": {
        "locationType": "SPREADSHEET",
        "spreadsheet": true
      },
      "visibility": "DOCUMENT"
    }
  ]
}

Sample situation 2:

As other situation, it supposes that it creates new Spreadsheet, and create new developer metadata to the 1st column (column "A") as the key of "sampleKey" and value of "sampleValue".

In this case, the sample request body is as follows.

{
  "requests": [
    {
      "createDeveloperMetadata": {
        "developerMetadata": {
          "location": {
            "dimensionRange": {
              "sheetId": 0,
              "startIndex": 0,
              "endIndex": 1,
              "dimension": "COLUMNS"
            }
          },
          "metadataKey": "sampleKey",
          "metadataValue": "sampleValue",
          "visibility": "DOCUMENT"
        }
      }
    }
  ]
}

Sample curl command:

When you retrieve the developer metadata from above sample Spreadsheet, please use the following curl command.

curl "https://sheets.googleapis.com/v4/spreadsheets/### spreadsheetId ###?key=### your API key ###&fields=sheets(data(columnMetadata(developerMetadata)))"

• In this case, sheets(data(columnMetadata(developerMetadata))) is used to make it easier to see the response value. Of course, you can also use * as fields.

Result:

{
  "sheets": [
    {
      "data": [
        {
          "columnMetadata": [
            {
              "developerMetadata": [
                {
                  "metadataId": 123456789,
                  "metadataKey": "sampleKey",
                  "metadataValue": "sampleValue",
                  "location": {
                    "locationType": "COLUMN",
                    "dimensionRange": {
                      "dimension": "COLUMNS",
                      "startIndex": 0,
                      "endIndex": 1
                    }
                  },
                  "visibility": "DOCUMENT"
                }
              ]
            },
            {},
            ,
            ,
          ]
        }
      ]
    }
  ]
}

References:

• Method: spreadsheets.developerMetadata.get
• DeveloperMetadataVisibility

If I misunderstood your question and this was not the direction you want, I apologize.