Odczytywanie, zapisywanie i wyszukiwanie metadanych

Funkcja metadanych umożliwia powiązanie metadanych z różnymi encjami i lokalizacjami w arkuszu kalkulacyjnym. Następnie możesz wysyłać zapytania o te metadane i używać ich do znajdowania obiektów, z którymi są powiązane.

Metadane możesz powiązać z wierszami, kolumnami, arkuszami lub arkuszem kalkulacyjnym.

Informacje o metadanych

Poniżej opisujemy najważniejsze aspekty metadanych, które należy wziąć pod uwagę podczas pracy z interfejsem Sheets API:

  1. Metadane jako tagi: jednym z zastosowań metadanych dewelopera jest tag, który określa lokalizację w arkuszu kalkulacyjnym za pomocą tylko klucza i lokalizacji. Możesz na przykład powiązać tag headerRow z konkretnym wierszem lub tag totals z konkretną kolumną w arkuszu. Tagi mogą służyć do semantycznego powiązania części arkusza kalkulacyjnego z polami w narzędziu lub bazie danych innej firmy, dzięki czemu zmiany w arkuszu nie spowodują awarii aplikacji.

  2. Metadane jako właściwości: metadane utworzone przez określenie klucza, lokalizacji, i wartości działają jak para klucz-wartość powiązana z tą lokalizacją w arkuszu. Możesz na przykład powiązać:

    • formResponseId = resp123 z wierszem,
    • lastUpdated = 1477369882 z kolumną.

    Umożliwia to przechowywanie i uzyskiwanie dostępu do niestandardowych właściwości nazwanych powiązanych z określonymi obszarami lub danymi w arkuszu kalkulacyjnym.

  3. Metadane widoczne w projekcie i dokumencie: aby uniemożliwić jednemu projektowi dewelopera ingerowanie w metadane innego projektu, dostępne są 2 ustawienia visibility metadanych: project i document. W przypadku interfejsu Sheets API metadane project są widoczne i dostępne tylko w projekcie w chmurze Google, w którym zostały utworzone. Metadane document są dostępne z dowolnego projektu Google Cloud, który ma dostęp do dokumentu.

    Zapytania, które nie określają wyraźnie parametru visibility, zwracają pasujące metadane document i pasujące metadane project dla projektu w chmurze Google Cloud, który wysyła żądanie.

  4. Unikalność: klucze metadanych nie muszą być unikalne, ale metadataId musi być unikalny. Jeśli utworzysz metadane i nie określisz ich pola identyfikatora, interfejs API przypisze identyfikator. Ten identyfikator może służyć do identyfikowania metadanych, a klucze i inne atrybuty – do identyfikowania zestawów metadanych.

  5. Zwracanie metadanych w żądaniach do interfejsu API: obiekt DataFilter jest częścią wywołania interfejsu API, które opisuje dane do wybrania lub zwrócenia w żądaniu do interfejsu API.

    Pojedynczy obiekt DataFilter może określać tylko 1 typ kryteriów filtrowania do znajdowania danych:

    • developerMetadataLookup: wybiera dane powiązane z określonymi metadanymi dewelopera, które spełniają kryteria.

    • a1Range: wybiera dane, które pasują do określonego zakresu w notacji A1 . Na przykład Sheet1!A1:B10.

    • gridRange: wybiera dane, które pasują do określonego zakresu siatki, używając indeksów opartych na zerze. Na przykład Sheet1!A3:B4 == sheetId: 123456, startRowIndex: 2, endRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2.

    Aby filtrować według wielu lokalizacji lub kryteriów, możesz użyć wielu DataFilter obiektów w jednym żądaniu do interfejsu API. W przypadku żądania zbiorczego, takiego jak metoda spreadsheets.values.batchGetByDataFilter, podaj tablicę lub listę obiektów DataFilter. Każdy zakres, który pasuje do dowolnego z filtrów danych w żądaniu, zostanie zwrócony lub zmodyfikowany.

    Więcej informacji znajdziesz w artykule Odczytywanie i zapisywanie wartości powiązanych z metadanymi.

Przypadki użycia

Oto kilka przykładowych przypadków użycia do zarządzania metadanymi:

  • Powiązywanie dowolnych danych z różnymi encjami i lokalizacjami w arkuszu kalkulacyjnym: na przykład powiązanie totals z kolumną D lub responseId = 1234 z wierszem 7.

  • Znajdowanie wszystkich lokalizacji i danych powiązanych z określonym kluczem lub atrybutem metadanych: na przykład w przypadku klucza totals powiązanego z kolumną D lub w przypadku tagu responseId zwracanie wszystkich wierszy z metadanymi responseId i powiązaną z nimi wartością metadanych.

  • Znajdowanie wszystkich danych powiązanych z określoną encją lub lokalizacją: na przykład w przypadku kolumny D zwracanie wszystkich metadanych powiązanych z tą lokalizacją.

  • Pobieranie wartości w lokalizacji przez określenie powiązanych metadanych: Na przykład w przypadku tagu totals zwracanie reprezentacji wartości zawartych w powiązanej kolumnie lub wierszu albo w przypadku tagu summary zwracanie reprezentacji powiązanego zasobu arkusza.

  • Aktualizowanie wartości w lokalizacji przez określenie powiązanych metadanych: na przykład zamiast aktualizować wartości w wierszu za pomocą notacji A1, aktualizuj wartości, wskazując identyfikator metadanych.

Odczytywanie i zapisywanie metadanych

Zasób spreadsheets.developerMetadata zapewnia dostęp do metadanych powiązanych z lokalizacją lub obiektem w arkuszu kalkulacyjnym. Metadane dewelopera mogą służyć do powiązania dowolnych danych z różnymi częściami arkusza kalkulacyjnego. Metadane pozostają powiązane z tymi lokalizacjami podczas edytowania arkusza kalkulacyjnego.

Tworzenie metadanych

Aby utworzyć metadane, użyj batchUpdate metody w zasobie spreadsheets, i podaj wartości metadataKey, location i visibility z zasobu spreadsheets.developerMetadata w elemencie CreateDeveloperMetadataRequest. Opcjonalnie możesz określić metadataValue lub wyraźny metadataId.

Jeśli podasz identyfikator, który jest już używany, żądanie zakończy się niepowodzeniem. Jeśli nie podasz identyfikatora, interfejs API przypisze go.

W tym przykładzie w żądaniu podajemy klucz, wartość i wiersz. Odpowiedź zwraca te wartości metadanych dewelopera oraz przypisany identyfikator metadanych.

Żądanie

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

Odpowiedź

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

Odczytywanie pojedynczego elementu metadanych

Aby pobrać pojedyncze, odrębne metadane dewelopera, użyj metody spreadsheets.developerMetadata.get, określając spreadsheetId zawierający metadane oraz unikalny metadataId metadanych dewelopera.

Żądanie

W tym przykładzie w żądaniu podajemy identyfikator arkusza kalkulacyjnego i identyfikator metadanych. Odpowiedź zwraca wartości metadanych dewelopera dla identyfikatora metadanych.

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

Odpowiedź

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

Odczytywanie wielu elementów metadanych

Aby pobrać wiele elementów metadanych dewelopera, użyj spreadsheets.developerMetadata.search metody. Musisz określić DataFilter który pasuje do dowolnych istniejących metadanych w dowolnej kombinacji właściwości, takich jak klucz, wartość, lokalizacja lub widoczność.

W tym przykładzie w żądaniu podajemy kilka identyfikatorów metadanych. Odpowiedź zwraca wartości metadanych dewelopera dla każdego identyfikatora metadanych.

Żądanie

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

Odpowiedź

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

Aktualizowanie metadanych

Aby zaktualizować metadane dewelopera, użyj metody spreadsheets.batchUpdate i podaj UpdateDeveloperMetadataRequest. Musisz określić DataFilter, który kieruje na metadane do zaktualizowania, zasób spreadsheets.developerMetadata z nowymi wartościami oraz maskę pola opisującą pola do zaktualizowania.

W tym przykładzie w żądaniu podajemy identyfikator metadanych, identyfikator arkusza i nowy klucz metadanych. Odpowiedź zwraca te wartości metadanych dewelopera oraz zaktualizowany klucz metadanych.

Żądanie

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

Odpowiedź

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

Usuwanie metadanych

Aby usunąć metadane dewelopera, użyj metody batchUpdate i podaj DeleteDeveloperMetadataRequest. Musisz określić DataFilter, aby wybrać metadane, które chcesz usunąć.

W tym przykładzie w żądaniu podajemy identyfikator metadanych. Odpowiedź zwraca wartości metadanych dewelopera dla identyfikatora metadanych.

Aby potwierdzić usunięcie metadanych dewelopera, użyj metody spreadsheets.developerMetadata.get, określając usunięty identyfikator metadanych. Powinna zostać zwrócona odpowiedź z kodem stanu HTTP 404: Not Found i komunikatem „No developer metadata with ID METADATA_ID” (Nie ma metadanych dewelopera o identyfikatorze).

Żądanie

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

Odpowiedź

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

Odczytywanie i zapisywanie wartości powiązanych z metadanymi

Możesz też pobierać i aktualizować wartości komórek w wierszach i kolumnach, określając powiązane metadane dewelopera oraz wartości, które chcesz zaktualizować. Aby to zrobić, użyj jednej z tych metod z pasującym DataFilter.

Pobieranie wartości komórek według metadanych

Aby pobrać wartości komórek według metadanych, użyj spreadsheets.values.batchGetByDataFilter metody. Musisz określić identyfikator arkusza kalkulacyjnego oraz co najmniej 1 filtr danych, który pasuje do metadanych.

W tym przykładzie w żądaniu podajemy identyfikator metadanych. Odpowiedź zwraca wartości komórek wiersza (numer modelu, miesięczna sprzedaż) dla identyfikatora metadanych.

Żądanie

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

Odpowiedź

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

Pobieranie arkusza kalkulacyjnego według metadanych

Podczas pobierania arkusza kalkulacyjnego możesz zwrócić podzbiór danych za pomocą metody spreadsheets.getByDataFilter. Musisz określić identyfikator arkusza kalkulacyjnego oraz co najmniej 1 filtr danych, który pasuje do metadanych.

To żądanie działa jak zwykłe żądanie „GET arkusza kalkulacyjnego”, z tym że lista metadanych pasujących do określonych filtrów danych określa, które arkusze, dane siatki i inne zasoby obiektów z metadanymi są zwracane. Jeśli includeGridData ma wartość true, dla arkusza zwracane są też dane siatki przecinające określone zakresy siatki. Pole includeGridData jest ignorowane, jeśli w żądaniu jest ustawiona maska pola.

W tym przykładzie w żądaniu podajemy identyfikator metadanych i ustawiamy parametr includeGridData na false. Odpowiedź zwraca właściwości arkusza kalkulacyjnego i arkusza.

Żądanie

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

Odpowiedź

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

Aktualizowanie wartości według metadanych

Aby zaktualizować wartości komórek pasujące do określonych metadanych, użyj spreadsheets.values.batchUpdateByDataFilter metody. Musisz określić identyfikator arkusza kalkulacyjnego, valueInputOption, oraz co najmniej 1 DataFilterValueRange wartość, która pasuje do metadanych.

W tym przykładzie w żądaniu podajemy identyfikator metadanych i zaktualizowane wartości wiersza. Odpowiedź zwraca zaktualizowane właściwości i dane dla identyfikatora metadanych.

Żądanie

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

Odpowiedź

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

Czyszczenie wartości według metadanych

Aby wyczyścić wartości komórek pasujące do określonych metadanych, użyj spreadsheets.values.batchClearByDataFilter metody. Musisz określić filtr danych, aby wybrać metadane, które chcesz wyczyścić.

Żądanie

W tym przykładzie w żądaniu podajemy identyfikator metadanych. Odpowiedź zwraca identyfikator arkusza kalkulacyjnego i wyczyszczone zakresy.

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

Odpowiedź

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

Limity miejsca na metadane

Obowiązuje limit łącznej ilości metadanych, które można przechowywać w arkuszu kalkulacyjnym. Ten limit jest mierzony w znakach i składa się z 2 komponentów:

Element Limit miejsca na dane
Arkusze kalkulacyjne 30 000 znaków
Każdy arkusz w arkuszu kalkulacyjnym 30 000 znaków

W arkuszu kalkulacyjnym możesz przechowywać do 30 000 znaków. Dodatkowo możesz przechowywać 30 000 znaków w każdym arkuszu w arkuszu kalkulacyjnym (30 000 w arkuszu 1, 30 000 w arkuszu 2 itd.). Arkusze kalkulacyjne z 3 arkuszami mogą więc zawierać do 120 000 znaków metadanych.

Do tego limitu wliczają się wszystkie znaki w polach metadataKey i metadataValue zasobu spreadsheets.developerMetadata.