Lire, écrire et rechercher des métadonnées

La fonctionnalité de métadonnées vous permet d'associer des métadonnées à différentes entités et différents emplacements d'une feuille de calcul. Vous pouvez ensuite interroger ces métadonnées et les utiliser pour trouver les objets auxquels elles sont associées.

Vous pouvez associer des métadonnées à des lignes, des colonnes, des feuilles ou une feuille de calcul.

À propos des métadonnées

Voici quelques aspects clés des métadonnées à prendre en compte lorsque vous utilisez l'API Sheets :

  1. Métadonnées en tant que balises : l'une des utilisations des métadonnées de développement est une balise qui nomme un emplacement dans la feuille de calcul à l'aide d'une clé et d'un emplacement uniquement. Par exemple, vous pouvez associer headerRow à une ligne particulière ou totals à une colonne particulière d'une feuille. Les balises peuvent être utilisées pour lier sémantiquement des parties d'une feuille de calcul à des champs d'un outil ou d'une base de données tiers. Ainsi, les modifications apportées à la feuille de calcul n'affecteront pas votre application.

  2. Métadonnées en tant que propriétés : les métadonnées créées en spécifiant une clé, un emplacement, et une valeur agissent comme une paire clé-valeur associée à cet emplacement dans une feuille. Par exemple, vous pouvez associer :

    • formResponseId = resp123 à une ligne
    • lastUpdated = 1477369882 à une colonne

    Cela vous permet de stocker et d'accéder à des propriétés nommées personnalisées associées à des zones ou des données spécifiques d'une feuille de calcul.

  3. Métadonnées visibles au niveau du projet ou du document : pour éviter qu'un projet de développeur n'interfère avec les métadonnées d'un autre, il existe deux paramètres de métadonnées visibility : project et document. Avec l'API Sheets, les métadonnées project ne sont visibles et accessibles que depuis le projet Google Cloud qui les a créées. Les métadonnées document sont accessibles depuis n'importe quel projet Google Cloud ayant accès au document.

    Les requêtes qui ne spécifient pas explicitement de visibility renvoient les métadonnées document correspondantes et les métadonnées project correspondantes pour le projet Google Cloud qui effectue la requête.

  4. Unicité : les clés de métadonnées ne doivent pas nécessairement être uniques, mais le metadataId doit être distinct. Si vous créez des métadonnées et que vous laissez le champ d'ID non spécifié, l'API en attribue un. Cet ID peut être utilisé pour identifier les métadonnées, tandis que les clés et autres attributs peuvent être utilisés pour identifier des ensembles de métadonnées.

  5. Renvoi de métadonnées via des requêtes API : un DataFilter objet fait partie d'un appel d'API qui décrit les données à sélectionner ou à renvoyer à partir d' une requête API.

    Un seul objet DataFilter ne peut spécifier qu'un seul type de critère de filtre pour localiser les données :

    • developerMetadataLookup: sélectionne les données associées aux métadonnées de développement spécifiées qui correspondent aux critères.

    • a1Range : sélectionne les données qui correspondent à la plage de notation A1 spécifiée. Par exemple, Sheet1!A1:B10.

    • gridRange: sélectionne les données qui correspondent à la plage de grille spécifiée à l'aide d'index basés sur zéro. Par exemple, Sheet1!A3:B4 == sheetId: 123456, startRowIndex: 2, endRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2.

    Pour filtrer plusieurs emplacements ou critères, vous pouvez utiliser plusieurs DataFilter objets dans une seule requête API. Fournissez un tableau ou une liste d'DataFilter objets à une requête par lot, comme la spreadsheets.values.batchGetByDataFilter méthode. Toute plage correspondant à l'un des filtres de données de la requête sera renvoyée ou modifiée.

    Pour en savoir plus, consultez Lire et écrire des valeurs associées à des métadonnées.

Cas d'utilisation

Voici quelques exemples d'utilisation pour la gestion des métadonnées :

  • Associer des données arbitraires à différentes entités et différents emplacements d'une feuille de calcul : par exemple, associez totals à la colonne D ou responseId = 1234 à la ligne 7.

  • Rechercher tous les emplacements et toutes les données associés à une clé ou un attribut de métadonnées spécifique : par exemple, étant donné la clé totals associée à la colonne D ou l'élément responseId, renvoyez toutes les lignes contenant les métadonnées responseId et la valeur de métadonnées qui leur est associée.

  • Rechercher toutes les données associées à une entité ou un emplacement spécifique : par exemple, étant donné la colonne D, renvoyez toutes les métadonnées associées à cet emplacement.

  • Récupérer des valeurs dans un emplacement en spécifiant les métadonnées associées : par exemple, étant donné l'élément totals, renvoyez une représentation des valeurs contenues dans la colonne ou la ligne associée, ou étant donné un élément summary, renvoyez une représentation de la ressource de feuille associée.

  • Mettre à jour les valeurs dans un emplacement en spécifiant les métadonnées associées : par exemple, au lieu de mettre à jour les valeurs d'une ligne via la notation A1, mettez à jour les valeurs en indiquant un ID de métadonnées.

Lire et écrire des métadonnées

La ressource spreadsheets.developerMetadata permet d'accéder aux métadonnées associées à un emplacement ou à un objet d'une feuille de calcul. Les métadonnées de développement peuvent être utilisées pour associer des données arbitraires à différentes parties d'une feuille de calcul. Les métadonnées restent associées à ces emplacements lorsque la feuille de calcul est modifiée.

Créer des métadonnées

Pour créer des métadonnées, utilisez la batchUpdate méthode sur la spreadsheets ressource, puis fournissez une CreateDeveloperMetadataRequest avec metadataKey, location et visibility valeurs de la spreadsheets.developerMetadata ressource. Vous pouvez éventuellement spécifier une metadataValue ou un metadataId explicite.

Si vous spécifiez un ID déjà utilisé, la requête échouera. Si vous ne fournissez pas d'ID, l'API en attribue un.

Dans cet exemple, nous fournissons une clé, une valeur et une ligne dans la requête. La réponse renvoie ces valeurs de métadonnées de développement, ainsi que l'ID de métadonnées attribué.

Demande

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

Réponse

{
  "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"
        }
      }
    }
  ]
}

Lire un seul élément de métadonnées

Pour récupérer une seule métadonnée de développement distincte, utilisez la spreadsheets.developerMetadata.get méthode, en spécifiant le spreadsheetId contenant les métadonnées et le metadataId unique des métadonnées de développement.

Demande

Dans cet exemple, nous fournissons l'ID de la feuille de calcul et l'ID des métadonnées dans la requête. La réponse renvoie les valeurs de métadonnées de développement pour l'ID des métadonnées.

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

Réponse

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

Lire plusieurs éléments de métadonnées

Pour récupérer plusieurs éléments de métadonnées de développement, utilisez la spreadsheets.developerMetadata.search méthode. Vous devez spécifier un DataFilter qui correspond à toutes les métadonnées existantes sur n'importe quelle combinaison de propriétés telles que la clé, la valeur, l'emplacement ou la visibilité.

Dans cet exemple, nous fournissons plusieurs ID de métadonnées dans la requête. La réponse renvoie les valeurs de métadonnées de développement pour chaque ID de métadonnées.

Demande

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

Réponse

{
  "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
          }
        }
      ]
    }
  ]
}

Mettre à jour les métadonnées

Pour mettre à jour les métadonnées de développement, utilisez la spreadsheets.batchUpdate méthode et fournissez une UpdateDeveloperMetadataRequest. Vous devez spécifier un DataFilter qui cible les métadonnées à mettre à jour, une ressource spreadsheets.developerMetadata avec les nouvelles valeurs et un masque de champ décrivant les champs à mettre à jour.

Dans cet exemple, nous fournissons l'ID des métadonnées, l'ID de la feuille et une nouvelle clé de métadonnées dans la requête. La réponse renvoie ces valeurs de métadonnées de développement, ainsi que la clé de métadonnées mise à jour.

Demande

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

Réponse

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

Supprimer les métadonnées

Pour supprimer les métadonnées de développement, utilisez la batchUpdate méthode et fournissez une DeleteDeveloperMetadataRequest. Vous devez spécifier un DataFilter pour sélectionner les métadonnées que vous souhaitez supprimer.

Dans cet exemple, nous fournissons l'ID des métadonnées dans la requête. La réponse renvoie les valeurs de métadonnées de développement pour l'ID des métadonnées.

Pour confirmer que les métadonnées de développement sont supprimées, utilisez la spreadsheets.developerMetadata.get méthode, en spécifiant l'ID des métadonnées supprimées. Vous devriez recevoir une réponse avec le code d'état HTTP 404: Not Found et un message indiquant "Aucune métadonnée de développement ne porte l'ID METADATA_ID.

Demande

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

Réponse

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

Lire et écrire des valeurs associées à des métadonnées

Vous pouvez également récupérer et mettre à jour les valeurs des cellules dans les lignes et les colonnes en spécifiant les métadonnées de développement associées et les valeurs que vous souhaitez mettre à jour. Pour ce faire, utilisez l'une des méthodes suivantes avec un DataFilter correspondant.

Obtenir les valeurs des cellules par métadonnées

Pour obtenir les valeurs des cellules par métadonnées, utilisez la spreadsheets.values.batchGetByDataFilter méthode. Vous devez spécifier l'ID de la feuille de calcul et un ou plusieurs filtres de données qui correspondent aux métadonnées.

Dans cet exemple, nous fournissons l'ID des métadonnées dans la requête. La réponse renvoie les valeurs des cellules de la ligne (numéro de modèle, ventes mensuelles) pour l'ID des métadonnées.

Demande

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

Réponse

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

Obtenir une feuille de calcul par métadonnées

Lorsque vous récupérez une feuille de calcul, vous pouvez renvoyer un sous-ensemble de données à l'aide de la spreadsheets.getByDataFilter méthode. Vous devez spécifier l'ID de la feuille de calcul et un ou plusieurs filtres de données qui correspondent aux métadonnées.

Cette requête fonctionne comme une requête "GET de feuille de calcul" standard, à ceci près que la liste des métadonnées correspondant aux filtres de données spécifiés détermine les feuilles, les données de grille et les autres ressources d'objet avec des métadonnées qui sont renvoyées. Si includeGridData est défini sur true, les données de grille qui croisent les plages de grille spécifiées sont également renvoyées pour la feuille. Le champ includeGridData est ignoré si un masque de champ est défini dans la requête.

Dans cet exemple, nous fournissons l'ID des métadonnées et définissons includeGridData sur false dans la requête. La réponse renvoie les propriétés de la feuille de calcul et de la feuille.

Demande

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

Réponse

{
  "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
}

Mettre à jour les valeurs par métadonnées

Pour mettre à jour les valeurs des cellules correspondant à des métadonnées spécifiques, utilisez la spreadsheets.values.batchUpdateByDataFilter méthode. Vous devez spécifier l'ID de la feuille de calcul, valueInputOption, et une ou plusieurs DataFilterValueRange valeurs qui correspondent aux métadonnées.

Dans cet exemple, nous fournissons l'ID des métadonnées et les valeurs de ligne mises à jour dans la requête. La réponse renvoie les propriétés et les données mises à jour pour l'ID des métadonnées.

Demande

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

Réponse

{
  "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"
          ]
        ]
      }
    }
  ]
}

Effacer les valeurs par métadonnées

Pour effacer les valeurs des cellules correspondant à des métadonnées spécifiques, utilisez la spreadsheets.values.batchClearByDataFilter méthode. Vous devez spécifier un filtre de données pour sélectionner les métadonnées que vous souhaitez effacer.

Demande

Dans cet exemple, nous fournissons l'ID des métadonnées dans la requête. La réponse renvoie l'ID de la feuille de calcul et les plages effacées.

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

Réponse

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

Limites de stockage des métadonnées

La quantité totale de métadonnées que vous pouvez stocker dans une feuille de calcul est limitée. Cette limite est mesurée en caractères et se compose de deux éléments :

Élément Allocation de la limite de stockage
Feuille de calcul 30 000 caractères
Chaque feuille d'une feuille de calcul 30 000 caractères

Vous pouvez stocker jusqu'à 30 000 caractères pour la feuille de calcul. De plus, vous pouvez stocker 30 000 caractères pour chaque feuille d'une feuille de calcul (30 000 pour la première feuille, 30 000 pour la deuxième, etc.). Ainsi,une feuille de calcul comportant trois feuilles peut contenir jusqu'à 120 000 caractères de métadonnées.

Chaque caractère des champs metadataKey et metadataValue de la ressource spreadsheets.developerMetadata est pris en compte dans cette limite.