Leggere, scrivere e cercare metadati

La funzionalità dei metadati ti consente di associare i metadati a varie entità e posizioni in un foglio di lavoro. Puoi quindi eseguire query su questi metadati e utilizzarli per trovare gli oggetti a cui sono associati.

Puoi associare i metadati a righe, colonne, fogli o a un foglio di lavoro.

Informazioni sui metadati

Di seguito sono descritti alcuni aspetti chiave dei metadati da tenere in considerazione quando utilizzi l'API Google Sheets:

  1. Metadati come tag: un utilizzo dei metadati dello sviluppatore è come tag che nomina una posizione nel foglio di lavoro utilizzando solo una chiave e una posizione. Ad esempio, puoi associare headerRow a una riga specifica o totals a una colonna specifica all'interno di un foglio. I tag possono essere utilizzati per associare semanticamente porzioni di un foglio di lavoro a campi in uno strumento o database di terze parti, in modo che le modifiche al foglio di lavoro non interrompano l'app.

  2. Metadati come proprietà: i metadati creati specificando una chiave, una posizione, e un valore fungono da coppia chiave-valore associata a quella posizione in un foglio. Ad esempio, puoi associare:

    • formResponseId = resp123 a una riga
    • lastUpdated = 1477369882 a una colonna

    In questo modo puoi archiviare e accedere alle proprietà denominate personalizzate associate a determinate aree o dati in un foglio di lavoro.

  3. Metadati visibili a livello di progetto o documento: per impedire che un progetto dello sviluppatore interferisca con i metadati di un altro, esistono due impostazioni visibility dei metadati: project e document. Utilizzando l'API Sheets, i metadati project sono visibili e accessibili solo dal progetto Google Cloud che li ha creati. I metadati document sono accessibili da qualsiasi progetto Google Cloud con accesso al documento.

    Le query che non specificano esplicitamente una visibility restituiscono i metadati document corrispondenti e i metadati project corrispondenti per il progetto Google Cloud che effettua la richiesta.

  4. Unicità: le chiavi dei metadati non devono essere univoche, ma metadataId deve essere distinto. Se crei metadati e lasci il campo ID non specificato, l'API ne assegna uno. Questo ID può essere utilizzato per identificare i metadati, mentre le chiavi e altri attributi possono essere utilizzati per identificare i set di metadati.

  5. Restituisci i metadati tramite le richieste API: un DataFilter oggetto fa parte di una chiamata API che descrive i dati da selezionare o restituire da una richiesta API.

    Un singolo oggetto DataFilter può specificare un solo tipo di criteri di filtro per individuare i dati:

    • developerMetadataLookup: seleziona i dati associati ai metadati dello sviluppatore specificati che corrispondono ai criteri.

    • a1Range: Seleziona i dati che corrispondono all'intervallo di notazione A1 specificato. Ad esempio, Sheet1!A1:B10.

    • gridRange: seleziona i dati che corrispondono all'intervallo della griglia specificato utilizzando indici basati su zero. Ad esempio, Sheet1!A3:B4 == sheetId: 123456, startRowIndex: 2, endRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2.

    Per filtrare in base a più posizioni o criteri, puoi utilizzare più DataFilter oggetti in una singola richiesta API. Fornisci un array o un elenco di DataFilter oggetti a una richiesta batch come il spreadsheets.values.batchGetByDataFilter metodo. Qualsiasi intervallo che corrisponde a uno dei filtri dati nella richiesta verrà restituito o modificato.

    Per ulteriori informazioni, vedi Leggere e scrivere i valori associati ai metadati.

Casi d'uso

Di seguito sono riportati alcuni casi d'uso di esempio per la gestione dei metadati:

  • Associa dati arbitrari a varie entità e posizioni in un foglio di lavoro: ad esempio, associa totals alla colonna D o responseId = 1234 alla riga 7.

  • Trova tutte le posizioni e i dati associati a una determinata chiave o attributo: ad esempio, data la chiave totals associata alla colonna D o dato responseId, restituisci tutte le righe con i metadati responseId e il valore dei metadati associato.

  • Trova tutti i dati associati a una determinata entità o posizione: ad esempio, data la colonna D, restituisci tutti i metadati associati a quella posizione.

  • Recupera i valori in una posizione specificando i metadati associati: ad esempio, dato totals, restituisci una rappresentazione dei valori contenuti nella colonna o nella riga associata oppure, dato summary, restituisci una rappresentazione della risorsa Sheet associata.

  • Aggiorna i valori in una posizione specificando i metadati associati: ad esempio, anziché aggiornare i valori in una riga tramite la notazione A1, aggiorna i valori indicando un ID metadati.

Leggere e scrivere i metadati

La risorsa spreadsheets.developerMetadata fornisce l'accesso ai metadati associati a una posizione o a un oggetto in un foglio di lavoro. I metadati dello sviluppatore possono essere utilizzati per associare dati arbitrari a varie parti di un foglio di lavoro. I metadati rimangono associati a queste posizioni man mano che il foglio di lavoro viene modificato.

Creare metadati

Per creare metadati, utilizza il batchUpdate metodo sulla spreadsheets risorsa, e fornisci un CreateDeveloperMetadataRequest con metadataKey, location e visibility valori dalla spreadsheets.developerMetadata risorsa. Facoltativamente, puoi specificare un metadataValue o un metadataId esplicito.

Se specifichi un ID già in uso, la richiesta non andrà a buon fine. Se non fornisci un ID, l'API ne assegna uno.

In questo esempio, forniamo una chiave, un valore e una riga nella richiesta. La risposta restituisce questi valori dei metadati dello sviluppatore, oltre all'ID dei metadati assegnato.

Richiesta

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

Risposta

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

Leggere un singolo elemento di metadati

Per recuperare un singolo metadato dello sviluppatore distinto, utilizza il spreadsheets.developerMetadata.get metodo, specificando il spreadsheetId contenente i metadati e l'univoco metadataId dei metadati dello sviluppatore.

Richiesta

In questo esempio, forniamo l'ID del foglio di lavoro e l'ID dei metadati nella richiesta. La risposta restituisce i valori dei metadati dello sviluppatore per l'ID dei metadati.

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

Risposta

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

Leggere più elementi di metadati

Per recuperare più elementi di metadati dello sviluppatore, utilizza il spreadsheets.developerMetadata.search metodo. Devi specificare un DataFilter che corrisponda a qualsiasi metadato esistente su qualsiasi combinazione di proprietà come chiave, valore, posizione o visibilità.

In questo esempio, forniamo più ID dei metadati nella richiesta. La risposta restituisce i valori dei metadati dello sviluppatore per ogni ID dei metadati.

Richiesta

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

Risposta

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

Aggiornare i metadati

Per aggiornare i metadati dello sviluppatore, utilizza il spreadsheets.batchUpdate metodo e fornisci un UpdateDeveloperMetadataRequest. Devi specificare un DataFilter che abbia come target i metadati da aggiornare, una spreadsheets.developerMetadata risorsa con i nuovi valori e una maschera di campo che descriva i campi da aggiornare.

In questo esempio, forniamo l'ID dei metadati, l'ID del foglio e una nuova chiave dei metadati nella richiesta. La risposta restituisce questi valori dei metadati dello sviluppatore, oltre alla chiave dei metadati aggiornata.

Richiesta

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

Risposta

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

Eliminare i metadati

Per eliminare i metadati dello sviluppatore, utilizza il batchUpdate metodo e fornisci un DeleteDeveloperMetadataRequest. Devi specificare un DataFilter per selezionare i metadati da eliminare.

In questo esempio, forniamo l'ID dei metadati nella richiesta. La risposta restituisce i valori dei metadati dello sviluppatore per l'ID dei metadati.

Per verificare che i metadati dello sviluppatore siano stati rimossi, utilizza il spreadsheets.developerMetadata.get metodo, specificando l'ID dei metadati eliminati. Dovresti ricevere una risposta con codice di stato HTTP 404: Not Found, con un messaggio che indica "No developer metadata with ID METADATA_ID.

Richiesta

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

Risposta

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

Leggere e scrivere i valori associati ai metadati

Puoi anche recuperare e aggiornare i valori delle celle in righe e colonne specificando i metadati dello sviluppatore associati e i valori che vuoi aggiornare. Per farlo, utilizza uno dei seguenti metodi con un DataFilter corrispondente.

Recuperare i valori delle celle in base ai metadati

Per recuperare i valori delle celle in base ai metadati, utilizza il spreadsheets.values.batchGetByDataFilter metodo. Devi specificare l'ID del foglio di lavoro e uno o più filtri dati che corrispondono ai metadati.

In questo esempio, forniamo l'ID dei metadati nella richiesta. La risposta restituisce i valori delle celle della riga (numero di modello, vendite mensili) per l'ID dei metadati.

Richiesta

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

Risposta

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

Recuperare il foglio di lavoro in base ai metadati

Quando recuperi un foglio di lavoro, puoi restituire un sottoinsieme di dati utilizzando il spreadsheets.getByDataFilter metodo. Devi specificare l'ID del foglio di lavoro e uno o più filtri dati che corrispondono ai metadati.

Questa richiesta funziona come una normale richiesta "spreadsheet GET", tranne per il fatto che l'elenco dei metadati corrispondenti ai filtri dati specificati determina quali fogli, dati della griglia e altre risorse oggetto con metadati vengono restituiti. Se includeGridData è impostato su true, vengono restituiti anche i dati della griglia che si intersecano con gli intervalli della griglia specificati per il foglio. Il campo includeGridData viene ignorato se nella richiesta è impostata una maschera di campo.

In questo esempio, forniamo l'ID dei metadati e impostiamo includeGridData su false nella richiesta. La risposta restituisce sia le proprietà del foglio di lavoro sia quelle del foglio.

Richiesta

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

Risposta

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

Aggiornare i valori in base ai metadati

Per aggiornare i valori delle celle che corrispondono a metadati specifici, utilizza il spreadsheets.values.batchUpdateByDataFilter metodo. Devi specificare l'ID del foglio di lavoro, valueInputOption, e uno o più DataFilterValueRange valori che corrispondono ai metadati.

In questo esempio, forniamo l'ID dei metadati e i valori delle righe aggiornati nella richiesta. La risposta restituisce sia le proprietà aggiornate sia i dati per l'ID dei metadati.

Richiesta

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

Risposta

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

Cancellare i valori in base ai metadati

Per cancellare i valori delle celle che corrispondono a metadati specifici, utilizza il spreadsheets.values.batchClearByDataFilter metodo. Devi specificare un filtro dati per selezionare i metadati da cancellare.

Richiesta

In questo esempio, forniamo l'ID dei metadati nella richiesta. La risposta restituisce l'ID del foglio di lavoro e gli intervalli cancellati.

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

Risposta

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

Limiti di archiviazione dei metadati

Esiste un limite alla quantità totale di metadati che puoi archiviare in un foglio di lavoro. Questo limite è misurato in caratteri ed è composto da due componenti:

Elemento Allocazione del limite di archiviazione
Foglio di lavoro 30.000 caratteri
Ogni foglio all'interno di un foglio di lavoro 30.000 caratteri

Puoi archiviare fino a 30.000 caratteri per il foglio di lavoro. Inoltre, puoi archiviare 30.000 caratteri per ogni foglio all'interno di un foglio di lavoro (30.000 per il foglio 1, 30.000 per il foglio 2 e così via). Pertanto,un foglio di lavoro con tre fogli può contenere fino a 120.000 caratteri di metadati.

Ogni carattere nei campi metadataKey e metadataValue della risorsa spreadsheets.developerMetadata viene conteggiato ai fini di questo limite.