Ler, gravar e pesquisar metadados

Com o recurso de metadados, é possível associar metadados a várias entidades e locais em uma planilha. Em seguida, é possível consultar esses metadados e usá-los para encontrar os objetos associados a eles.

É possível associar metadados a linhas, colunas, páginas ou uma planilha.

Sobre os metadados

A seguir, descrevemos alguns aspectos importantes dos metadados que você precisa considerar ao trabalhar com a API Google Sheets:

1. Metadados como tags: um uso dos metadados do desenvolvedor é uma tag que nomeia um local na planilha usando apenas uma chave e um local. Por exemplo, é possível associar headerRow a uma linha específica ou totals a uma coluna específica em uma planilha. As tags podem ser usadas para vincular semanticamente partes de uma planilha a campos em uma ferramenta ou banco de dados de terceiros. Assim, as mudanças na planilha não vão prejudicar seu app.

2. Metadados como propriedades: metadados criados especificando uma chave, um local e um valor que atuam como um par de chave-valor associado a esse local em uma planilha. Por exemplo, você pode associar:

   • formResponseId = resp123 com uma linha
   • lastUpdated = 1477369882 com uma coluna

   Isso permite armazenar e acessar propriedades nomeadas personalizadas associadas a áreas ou dados específicos em uma planilha.

3. Metadados visíveis do projeto x do documento: para evitar que um projeto de desenvolvedor interfira nos metadados de outro, há duas configurações de metadados visibility: project e document. Usando a API Google Sheets, os metadados project só ficam visíveis e acessíveis no projeto do Google Cloud que os criou. Os metadados document podem ser acessados de qualquer projeto do Google Cloud com acesso ao documento.

   Consultas que não especificam explicitamente um visibility retornam metadados document e project correspondentes para o projeto do Google Cloud que faz a solicitação.

   Não use metadados de desenvolvedor visíveis no projeto como um mecanismo de segurança ou para armazenar secrets. Os metadados podem ser expostos a usuários com acesso de leitura ao documento.

4. Unicidade: as chaves de metadados não precisam ser exclusivas, mas o metadataId precisa ser diferente. Se você criar metadados e deixar o campo de ID sem especificação, a API vai atribuir um. Esse ID pode ser usado para identificar os metadados, enquanto chaves e outros atributos podem ser usados para identificar conjuntos de metadados.

   Embora seja possível especificar seu próprio "metadataId" ao criar metadados, recomendamos que você permita que a API Google Sheets atribua um para você.

5. Retornar metadados por solicitações de API: um objeto DataFilter faz parte de uma chamada de API que descreve os dados a serem selecionados ou retornados de uma solicitação de API.

   Um único objeto DataFilter só pode especificar um tipo de critério de filtro para localizar dados:

   • developerMetadataLookup: Seleciona dados associados aos metadados do desenvolvedor especificados que correspondem aos critérios.

   • a1Range: seleciona dados que correspondem ao intervalo de notação A1 especificado. Por exemplo, Sheet1!A1:B10.

   • gridRange: seleciona dados que correspondem ao intervalo de grade especificado usando índices baseados em zero. Por exemplo, Sheet1!A3:B4 == sheetId: 123456, startRowIndex: 2, endRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2.

   Para filtrar em vários locais ou critérios, use vários objetos DataFilter em uma única solicitação de API. Forneça uma matriz ou lista de objetos DataFilter a uma solicitação em lote, como o método spreadsheets.values.batchGetByDataFilter. Qualquer intervalo que corresponda a um dos filtros de dados na solicitação será retornado ou modificado.

   Para mais informações, consulte Ler e gravar valores associados a metadados.

Casos de uso

Confira alguns exemplos de casos de uso para gerenciar metadados:

• Associe dados arbitrários a várias entidades e locais em uma planilha: por exemplo, associe totals à coluna D ou responseId = 1234 à linha 7.

• Encontrar todos os locais e dados associados a uma chave ou atributo de metadados específico: por exemplo, considerando a chave totals associada à coluna D ou o responseId, retorne todas as linhas com os metadados responseId e o valor de metadados associado a elas.

• Encontrar todos os dados associados a uma entidade ou local específico: por exemplo, dada a coluna D, retorne todos os metadados associados a esse local.

• Recuperar valores em um local especificando metadados associados: por exemplo, dado o totals, retorne uma representação dos valores contidos na coluna ou linha associada ou, dado um summary, retorne uma representação do recurso da planilha associada.

• Atualizar valores em um local especificando metadados associados: por exemplo, em vez de atualizar os valores em uma linha usando a notação A1, atualize os valores indicando um ID de metadados.

Ler e gravar metadados

O recurso spreadsheets.developerMetadata fornece acesso aos metadados associados a um local ou objeto em uma planilha. Os metadados do desenvolvedor podem ser usados para associar dados arbitrários a várias partes de uma planilha. Os metadados permanecem associados nesses locais à medida que a planilha é editada.

Criar metadados

Para criar metadados, use o método batchUpdate no recurso spreadsheets e forneça um CreateDeveloperMetadataRequest com valores metadataKey, location e visibility do recurso spreadsheets.developerMetadata. Você pode especificar um metadataValue ou um metadataId explícito.

Se você especificar um ID que já está em uso, a solicitação não será concluída. Se você não fornecer um ID, a API vai atribuir um.

Neste exemplo, fornecemos uma chave, um valor e uma linha na solicitação. A resposta retorna esses valores de metadados do desenvolvedor, além do ID de metadados atribuído.

Solicitação

{
  "requests": [
    {
      "createDeveloperMetadata": {
        "developerMetadata": {
          "location": {
            "dimensionRange": {
              "sheetId": SHEET_ID,
              "dimension": "ROWS",
              "startIndex": 6,
              "endIndex": 7
            }
          },
          "visibility": "DOCUMENT",
          "metadataKey": "Sales",
          "metadataValue": "2022"
        }
      }
    }
  ]
}

Resposta

{
  "spreadsheetId": SPREADSHEET_ID,
  "replies": [
    {
      "createDeveloperMetadata": {
        "developerMetadata": {
          "metadataId": METADATA_ID,
          "metadataKey": "Sales",
          "metadataValue": "2022",
          "location": {
            "locationType": "ROW",
            "dimensionRange": {
              "sheetId": SHEET_ID,
              "dimension": "ROWS",
              "startIndex": 6,
              "endIndex": 7
            }
          },
          "visibility": "DOCUMENT"
        }
      }
    }
  ]
}

Ler um único item de metadados

Para recuperar um único metadado de desenvolvedor distinto, use o método spreadsheets.developerMetadata.get, especificando o spreadsheetId que contém os metadados e o metadataId exclusivo dos metadados de desenvolvedor.

Solicitação

Neste exemplo, fornecemos o ID da planilha e o ID dos metadados na solicitação. A resposta retorna os valores de metadados do desenvolvedor para o ID de metadados.

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/developerMetadata/METADATA_ID

Resposta

{
  "metadataId": METADATA_ID,
  "metadataKey": "Sales",
  "metadataValue": "2022",
  "location": {
    "locationType": "ROW",
    "dimensionRange": {
      "sheetId": SHEET_ID,
      "dimension": "ROWS",
      "startIndex": 6,
      "endIndex": 7
    }
  },
  "visibility": "DOCUMENT"
}

Ler vários itens de metadados

Para recuperar vários itens de metadados do desenvolvedor, use o método spreadsheets.developerMetadata.search. É necessário especificar um DataFilter que corresponda a metadados existentes em qualquer combinação de propriedades, como chave, valor, local ou visibilidade.

Neste exemplo, fornecemos vários IDs de metadados na solicitação. A resposta retorna os valores de metadados do desenvolvedor para cada ID de metadados.

Solicitação

{
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataId": METADATA_ID
      }
    },
    {
      "developerMetadataLookup": {
        "metadataId": METADATA_ID
      }
    }
  ]
}

Resposta

{
  "matchedDeveloperMetadata": [
    {
      "developerMetadata": {
        "metadataId": METADATA_ID,
        "metadataKey": "Revenue",
        "metadataValue": "2022",
        "location": {
          "locationType": "SHEET",
          "sheetId": SHEET_ID
        },
        "visibility": "DOCUMENT"
      },
      "dataFilters": [
        {
          "developerMetadataLookup": {
            "metadataId": METADATA_ID
          }
        }
      ]
    },
    {
      "developerMetadata": {
        "metadataId": METADATA_ID,
        "metadataKey": "Sales",
        "metadataValue": "2022",
        "location": {
          "locationType": "SHEET",
          "sheetId": SHEET_ID
        },
        "visibility": "DOCUMENT"
      },
      "dataFilters": [
        {
          "developerMetadataLookup": {
            "metadataId": METADATA_ID
          }
        }
      ]
    }
  ]
}

Atualizar metadados

Para atualizar os metadados do desenvolvedor, use o método spreadsheets.batchUpdate e forneça um UpdateDeveloperMetadataRequest. Você precisa especificar um DataFilter que tenha como destino os metadados a serem atualizados, um recurso spreadsheets.developerMetadata com os novos valores e uma máscara de campo que descreva os campos a serem atualizados.

Neste exemplo, fornecemos o ID dos metadados, o ID da planilha e uma nova chave de metadados na solicitação. A resposta retorna esses valores de metadados do desenvolvedor, além da chave de metadados atualizada.

Solicitação

{
  "requests": [
    {
      "updateDeveloperMetadata": {
        "dataFilters": [
          {
            "developerMetadataLookup": {
              "metadataId": METADATA_ID
            }
          }
        ],
        "developerMetadata": {
          "location": {
            "sheetId": SHEET_ID
          },
          "metadataKey": "SalesUpdated"
        },
        "fields": "location,metadataKey"
      }
    }
  ]
}

Resposta

{
  "spreadsheetId": SPREADSHEET_ID,
  "replies": [
    {
      "updateDeveloperMetadata": {
        "developerMetadata": [
          {
            "metadataId": METADATA_ID,
            "metadataKey": "SalesUpdated",
            "metadataValue": "2022",
            "location": {
              "locationType": "SHEET",
              "sheetId": SHEET_ID
            },
            "visibility": "DOCUMENT"
          }
        ]
      }
    }
  ]
}

Excluir metadados

Para excluir metadados do desenvolvedor, use o método batchUpdate e forneça um DeleteDeveloperMetadataRequest. Você precisa especificar um DataFilter para selecionar os metadados que quer excluir.

Neste exemplo, fornecemos o ID dos metadados na solicitação. A resposta retorna os valores de metadados do desenvolvedor para o ID de metadados.

Para confirmar que os metadados do desenvolvedor foram removidos, use o método spreadsheets.developerMetadata.get e especifique o ID dos metadados excluídos. Você vai receber uma resposta com o código de status HTTP 404: Not Found e uma mensagem informando "Não há metadados do desenvolvedor com o ID METADATA_ID.

Solicitação

{
  "requests": [
    {
      "deleteDeveloperMetadata": {
        "dataFilter": {
          "developerMetadataLookup": {
            "metadataId": METADATA_ID
          }
        }
      }
    }
  ]
}

Resposta

{
  "spreadsheetId": SPREADSHEET_ID,
  "replies": [
    {
      "deleteDeveloperMetadata": {
        "deletedDeveloperMetadata": [
          {
            "metadataId": METADATA_ID,
            "metadataKey": "SalesUpdated",
            "metadataValue": "2022",
            "location": {
              "locationType": "SHEET",
              "sheetId": SHEET_ID
            },
            "visibility": "DOCUMENT"
          }
        ]
      }
    }
  ]
}

Ler e gravar valores associados a metadados

Também é possível recuperar e atualizar valores de células em linhas e colunas especificando os metadados do desenvolvedor associados e os valores que você quer atualizar. Para fazer isso, use um dos métodos a seguir com um DataFilter correspondente.

Receber valores de células por metadados

Para receber valores de células por metadados, use o método spreadsheets.values.batchGetByDataFilter. Você precisa especificar o ID da planilha e um ou mais filtros de dados que correspondam aos metadados.

Neste exemplo, fornecemos o ID dos metadados na solicitação. A resposta retorna os valores das células da linha (número do modelo, vendas mensais) para o ID dos metadados.

Solicitação

{
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataId": METADATA_ID
      }
    }
  ],
  "majorDimension": "ROWS"
}

Resposta

{
  "spreadsheetId": SPREADSHEET_ID,
  "valueRanges": [
    {
      "valueRange": {
        "range": "Sheet7!A7:Z7",
        "majorDimension": "ROWS",
        "values": [
          [
            "W-24",
            "74"
          ]
        ]
      },
      "dataFilters": [
        {
          "developerMetadataLookup": {
            "metadataId": METADATA_ID
          }
        }
      ]
    }
  ]
}

Receber planilha por metadados

Ao recuperar uma planilha, é possível retornar um subconjunto de dados usando o método spreadsheets.getByDataFilter. Você precisa especificar o ID da planilha e um ou mais filtros de dados que correspondam aos metadados.

Essa solicitação funciona como uma solicitação "GET de planilha" normal, exceto que a lista de metadados correspondentes aos filtros de dados especificados determina quais planilhas, dados de grade e outros recursos de objeto com metadados são retornados. Se includeGridData estiver definido como true, os dados da grade que se cruzam com os intervalos especificados também serão retornados para a planilha. O campo includeGridData será ignorado se uma máscara de campo for definida na solicitação.

Neste exemplo, fornecemos o ID dos metadados e definimos includeGridData como false na solicitação. A resposta retorna as propriedades da planilha e da página.

Solicitação

{
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataId": METADATA_ID
      }
    }
  ],
  "includeGridData": false
}

Resposta

{
  "spreadsheetId": SPREADSHEET_ID,
  "properties": {
    "title": "Sales Sheet",
    "locale": "en_US",
    "autoRecalc": "ON_CHANGE",
    "timeZone": "America/Los_Angeles",
    "defaultFormat": {
      "backgroundColor": {
        "red": 1,
        "green": 1,
        "blue": 1
      },
      "padding": {
        "top": 2,
        "right": 3,
        "bottom": 2,
        "left": 3
      },
      "verticalAlignment": "BOTTOM",
      "wrapStrategy": "OVERFLOW_CELL",
      "textFormat": {
        "foregroundColor": {},
        "fontFamily": "arial,sans,sans-serif",
        "fontSize": 10,
        "bold": false,
        "italic": false,
        "strikethrough": false,
        "underline": false,
        "foregroundColorStyle": {
          "rgbColor": {}
        }
      },
      "backgroundColorStyle": {
        "rgbColor": {
          "red": 1,
          "green": 1,
          "blue": 1
        }
      }
    },
    "spreadsheetTheme": {
      "primaryFontFamily": "Arial",
      "themeColors": [
        {
          "colorType": "TEXT",
          "color": {
            "rgbColor": {}
          }
        },
        {
          "colorType": "BACKGROUND",
          "color": {
            "rgbColor": {
              "red": 1,
              "green": 1,
              "blue": 1
            }
          }
        },
        {
          "colorType": "ACCENT1",
          "color": {
            "rgbColor": {
              "red": 0.25882354,
              "green": 0.52156866,
              "blue": 0.95686275
            }
          }
        },
        {
          "colorType": "ACCENT2",
          "color": {
            "rgbColor": {
              "red": 0.91764706,
              "green": 0.2627451,
              "blue": 0.20784314
            }
          }
        },
        {
          "colorType": "ACCENT3",
          "color": {
            "rgbColor": {
              "red": 0.9843137,
              "green": 0.7372549,
              "blue": 0.015686275
            }
          }
        },
        {
          "colorType": "ACCENT4",
          "color": {
            "rgbColor": {
              "red": 0.20392157,
              "green": 0.65882355,
              "blue": 0.3254902
            }
          }
        },
        {
          "colorType": "ACCENT5",
          "color": {
            "rgbColor": {
              "red": 1,
              "green": 0.42745098,
              "blue": 0.003921569
            }
          }
        },
        {
          "colorType": "ACCENT6",
          "color": {
            "rgbColor": {
              "red": 0.27450982,
              "green": 0.7411765,
              "blue": 0.7764706
            }
          }
        },
        {
          "colorType": "LINK",
          "color": {
            "rgbColor": {
              "red": 0.06666667,
              "green": 0.33333334,
              "blue": 0.8
            }
          }
        }
      ]
    }
  },
  "sheets": [
    {
      "properties": {
        "sheetId": SHEET_ID,
        "title": "Sheet7",
        "index": 7,
        "sheetType": "GRID",
        "gridProperties": {
          "rowCount": 1000,
          "columnCount": 26
        }
      }
    }
  ],
  "spreadsheetUrl": SPREADSHEET_URL
}

Atualizar valores por metadados

Para atualizar valores de células que correspondem a metadados específicos, use o método spreadsheets.values.batchUpdateByDataFilter. É necessário especificar o ID da planilha, valueInputOption, e um ou mais valores de DataFilterValueRange que correspondam aos metadados.

Neste exemplo, fornecemos o ID dos metadados e os valores de linha atualizados na solicitação. A resposta retorna as propriedades e os dados atualizados para o ID de metadados.

Solicitação

{
  "data": [
    {
      "dataFilter": {
        "developerMetadataLookup": {
          "metadataId": METADATA_ID
        }
      },
      "majorDimension": "ROWS",
      "values": [
        [
          "W-24",
          "84"
        ]
      ]
    }
  ],
  "includeValuesInResponse": true,
  "valueInputOption": "USER_ENTERED"
}

Resposta

{
  "spreadsheetId": SPREADSHEET_ID,
  "totalUpdatedRows": 1,
  "totalUpdatedColumns": 2,
  "totalUpdatedCells": 2,
  "totalUpdatedSheets": 1,
  "responses": [
    {
      "updatedRange": "Sheet7!A7:B7",
      "updatedRows": 1,
      "updatedColumns": 2,
      "updatedCells": 2,
      "dataFilter": {
        "developerMetadataLookup": {
          "metadataId": METADATA_ID
        }
      },
      "updatedData": {
        "range": "Sheet7!A7:Z7",
        "majorDimension": "ROWS",
        "values": [
          [
            "W-24",
            "84"
          ]
        ]
      }
    }
  ]
}

Limpar valores por metadados

Para limpar valores de células que correspondem a metadados específicos, use o método spreadsheets.values.batchClearByDataFilter. Você precisa especificar um filtro de dados para selecionar os metadados que quer limpar.

Solicitação

Neste exemplo, fornecemos o ID dos metadados na solicitação. A resposta retorna o ID da planilha e os intervalos limpos.

{
  "dataFilters": [
    {
      "developerMetadataLookup": {
        "metadataId": METADATA_ID
      }
    }
  ]
}

Resposta

{
  "spreadsheetId": SPREADSHEET_ID,
  "clearedRanges": [
    "Sheet7!A7:Z7"
  ]
}

Limites de armazenamento de metadados

Há um limite para a quantidade total de metadados que você pode armazenar em uma planilha. Esse limite é medido em caracteres e é composto de dois componentes:

Item	Alocação de limite de armazenamento
Planilha	30.000 caracteres
Cada página de uma planilha	30.000 caracteres

É possível armazenar até 30.000 caracteres na planilha. Além disso, é possível armazenar 30.000 caracteres em cada página de uma planilha (30.000 para a página 1, 30.000 para a página 2 e assim por diante). Assim,uma planilha com três páginas pode conter até 120.000 caracteres de metadados.

Cada caractere nos campos metadataKey e metadataValue do recurso spreadsheets.developerMetadata é contabilizado nesse limite.

Temas relacionados

• Aplicar filtros aos dados das Planilhas Google
• Gerenciar a visibilidade dos dados com filtros
• Limites de uso