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 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 tags : les métadonnées de développeur peuvent être utilisées comme tags pour nommer 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 spécifique ou totals à une colonne spécifique d'une feuille. Les tags peuvent être utilisés pour lier sémantiquement des parties d'une feuille de calcul à des champs dans un outil ou 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 avec une ligne
    • lastUpdated = 1477369882 avec une colonne

    Vous pouvez ainsi stocker des propriétés nommées personnalisées associées à des zones ou des données spécifiques d'une feuille de calcul, et y accéder.

  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 et 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 ne spécifiez pas leur champ d'ID, 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. Renvoyer des métadonnées via des requêtes API : un objet DataFilter 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 peut spécifier 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éveloppeur 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 lieux ou critères, vous pouvez utiliser plusieurs objets DataFilter dans une même requête API. Fournissez un tableau ou une liste d'objets DataFilter à une requête par lot comme la méthode spreadsheets.values.batchGetByDataFilter. 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 aux métadonnées.

Cas d'utilisation

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

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

  • Trouver tous les emplacements et 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 étant donné responseId, renvoyer toutes les lignes avec les métadonnées responseId et la valeur de métadonnées qui leur est associée.

  • Trouver toutes les données associées à une entité ou un lieu spécifique : par exemple, pour la colonne D, renvoyer toutes les métadonnées associées à ce lieu.

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

  • Mettre à jour les valeurs d'un lieu en spécifiant les métadonnées associées : par exemple, au lieu de mettre à jour les valeurs d'une ligne à l'aide de 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 lieu ou à un objet dans une feuille de calcul. Les métadonnées de développeur 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 méthode batchUpdate sur la ressource spreadsheets et fournissez un CreateDeveloperMetadataRequest avec les valeurs metadataKey, location et visibility de la ressource spreadsheets.developerMetadata. Vous pouvez éventuellement spécifier un 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éveloppeur, 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 des métadonnées de développeur uniques et distinctes, utilisez la méthode spreadsheets.developerMetadata.get en spécifiant le spreadsheetId contenant les métadonnées et le metadataId unique des métadonnées de développeur.

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 des 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éveloppeur, utilisez la méthode spreadsheets.developerMetadata.search. 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 des métadonnées de développeur 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 du développeur, utilisez la méthode spreadsheets.batchUpdate et fournissez un UpdateDeveloperMetadataRequest. Vous devez spécifier un DataFilter ciblant 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éveloppeur, 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 des métadonnées

Pour supprimer des métadonnées de développeur, utilisez la méthode batchUpdate et fournissez un 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 des métadonnées de développement pour l'ID des métadonnées.

Pour confirmer que les métadonnées du développeur ont été supprimées, utilisez la méthode spreadsheets.developerMetadata.get en spécifiant l'ID des métadonnées supprimées. Vous devriez recevoir un code d'état HTTP 404: Not Found, avec 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 aux 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éveloppeur 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 méthode spreadsheets.values.batchGetByDataFilter. Vous devez spécifier l'ID de la feuille de calcul et un ou plusieurs filtres de données correspondant 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 ligne (numéro de modèle, ventes mensuelles) pour l'ID de 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 méthode spreadsheets.getByDataFilter. Vous devez spécifier l'ID de la feuille de calcul et un ou plusieurs filtres de données correspondant aux métadonnées.

Cette requête fonctionne comme une requête "GET de feuille de calcul" standard, sauf 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 métadonnées qui sont renvoyées. Si includeGridData est défini sur true, les données de grille croisant 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 à la fois les propriétés de la feuille de calcul et celles 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 de cellules correspondant à des métadonnées spécifiques, utilisez la méthode spreadsheets.values.batchUpdateByDataFilter. Vous devez spécifier l'ID de la feuille de calcul, valueInputOption, et une ou plusieurs valeurs DataFilterValueRange correspondant 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 à la fois les propriétés et les données mises à jour pour l'ID de 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 de cellules correspondant à des métadonnées spécifiques, utilisez la méthode spreadsheets.values.batchClearByDataFilter. 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 exprimée en nombre de 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'un même tableur (30 000 pour la première feuille, 30 000 pour la deuxième, etc.). Une feuille de calcul comportant trois feuilles peut donc contenir jusqu'à 120 000 caractères de métadonnées.

Chaque caractère des champs metadataKey et metadataValue de la ressource spreadsheets.developerMetadata est comptabilisé dans cette limite.