Dieser Artikel stellt vor, wie man Sheets API, die API-Zugriff auf Google-Dienste bietet, und seine Bibliothek googleapis in node.js verwendet, um Sheets und Zellen zu lesen, in Zellen zu schreiben und Zellen in Google Spreadsheets zu aktualisieren.
Aktivierung, Erstellung und Konfiguration der Google Sheets API
Bevor Sie die Sheets API von node.js usw. über Credentials-JSON-Dateien und API-Schlüssel manipulieren können, müssen Sie die Sheets API von Ihrem Google-Konto aus aktivieren, einen neuen API-Schlüssel erstellen und OAuth konfigurieren.
Erstellen Sie ein Projekt, falls noch nicht geschehen
Beachten Sie, dass die folgenden Schritte sich beziehen auf:
Create a project and enable the API | Google Workspace for Developers
Greifen Sie auf Google Cloud Platform (GCP) zu und wählen Sie ein Projekt aus.
Wenn noch kein Projekt erstellt wurde, erstellen Sie ein neues.
Sheets API aktivieren
Aktivieren Sie Sheets API in Ihrem Konto. Öffnen Sie das Menü auf der linken Seite des Bildschirms und gehen Sie zu API and services > library.
Auf dem nächsten Bildschirm suchen Sie angemessen nach “sheets” usw., um Google Sheets API zu finden. Wenn Sie es gefunden haben, klicken Sie darauf, fahren Sie fort und aktivieren Sie es. Im Bild unten steht “manage”, aber wenn es noch nicht aktiviert wurde, sollte es etwa “enable” sagen.
OAuth 2.0 konfigurieren
Als nächstes konfigurieren Sie OAuth 2.0. Durch die Konfiguration von OAuth 2.0 können Sie API-Zugriffsberechtigung von beliebigen oder bestimmten Google-Konten, einschließlich Ihres eigenen, anfordern. OAuth ist der Mechanismus, der Ihr eigenes, das von verwandten Parteien oder Benutzern mit der API verknüpft, und wir aktivieren und konfigurieren das hier.
Beachten Sie, dass die folgenden Schritte sich beziehen auf:
Create credentials | Google Workspace for Developers
Öffnen Sie das Menü auf der linken Seite des Bildschirms und gehen Sie zu API and services > OAuth consent screen.
Konfigurieren Sie OAuth 2.0.
In Schritt 1’s OAuth consent screen geben Sie schnell App-Name, E-Mail-Adresse usw. wie gewünscht ein.
In Schritt 2’s scopes fügen Sie /auth/spreadsheets von Google Sheets API hinzu.
In Schritt 3’s Testbenutzer-Hinzufügung fügen Sie die E-Mail-Adresse des Google-Kontos hinzu, das Sie zum Testen verwenden möchten.
OAuth Client-ID Credential erstellen
Generieren Sie eine Client-ID und API-Schlüssel-JSON-Datei für die Verwendung von OAuth in node.js. Erstellen Sie neue Credentials vom oberen Rand des Bildschirms auf der Credentials-Seite.
Wählen Sie Desktop-App für den Anwendungstyp. Nach Abschluss der Erstellung speichern Sie die JSON-Datei. Diese Datei wird später im node.js-Programm geladen.
Google Sheets API von googleapis in node.js ausführen
Installieren Sie die googleapis-Bibliothek in node.
npm install googleapis@39 --saveBeachten Sie, dass die node.js- und Bibliotheksabhängigkeitsversionen meiner Umgebung wie folgt sind:
> node -v
v13.10.1{
"name": "tmp_dev",
"version": "1.0.0",
"description": "",
"main": "tmp_dev.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": "",
"license": "ISC",
"dependencies": {
"googleapis": "^39.2.0",
}
}Kopieren Sie das node.js-Programm von Step 2: Set up the sample > sheets/quickstart/index.js auf der folgenden Seite und speichern Sie es in einer Datei.
Node.js quickstart | Sheets API | Google Developers
Übergeben Sie die im obigen Schritt gespeicherte JSON-Datei an node.js. Geben Sie den Dateipfad im Teil “fs.readFile(‘credentials.json’” an.
Führen Sie die Datei mit node aus. Eine URL wird in der Konsole angezeigt, also klicken Sie darauf und authentifizieren Sie sich.
URL-Beispiel: accounts.google.com/o/oauth2/v2/auth…
Wenn die Authentifizierung abgeschlossen ist, wird das Token in der token.json-Datei zwischengespeichert, und von da an kann das Programm ohne Authentifizierung ausgeführt werden, es sei denn, Sie löschen token.json.
Wenn die folgende Ausgabe in der Konsole erscheint, haben Sie erfolgreich Spreadsheet-Zellen über die API abgerufen:
Name, Major:
Alexandra, English
Andrew, Math
Anna, EnglishSchreiben in Google Sheets API von googleapis
Oben haben wir Leseoperationen gemäß der Anleitung durchgeführt. Unten werde ich Code zum Schreiben und Aktualisieren (write) von Sheets vorstellen.
Als Beispieldaten verwenden wir Googles offizielles Beispiel-Sheet.
Kopieren Sie das Sheet, speichern Sie es in Ihrem Konto und machen Sie es bearbeitbar.
Wählen Sie die Sheet-ID des kopierten Sheets aus. Zum Beispiel wird es eine Kombination wie die folgende URL und Sheet-ID sein:
URL: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0
ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upmsUnten werde ich das Programm als Ganzes vorstellen. (Teilweise zitiert) Schreiben Sie das Folgende entsprechend um:
sheet_id .. Sheet-ID oauth_json .. Die im obigen Schritt gespeicherte OAuth 2.0 Client-ID API-Schlüssel-JSON-Datei (beginnend mit “client_secret_” zum Beispiel)
const fs = require('fs');
const readline = require('readline');
const { google } = require('googleapis');
// If modifying these scopes, delete token.json.
// const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const TOKEN_PATH = 'token.json';
fs.readFile(<<oauth_json>>, (err, content) => {
if (err) return console.log('Error loading client secret file:', err);
// Authorize a client with credentials, then call the Google Sheets API.
authorize(JSON.parse(content), main);
});
function authorize(credentials, callback) {
const { client_secret, client_id, redirect_uris } = credentials.installed;
const oAuth2Client = new google.auth.OAuth2(
client_id, client_secret, redirect_uris[0]);
// Check if we have previously stored a token.
fs.readFile(TOKEN_PATH, (err, token) => {
if (err) return getNewToken(oAuth2Client, callback);
oAuth2Client.setCredentials(JSON.parse(token));
callback(oAuth2Client);
});
}
function getNewToken(oAuth2Client, callback) {
const authUrl = oAuth2Client.generateAuthUrl({
access_type: 'offline',
scope: SCOPES,
});
console.log('Authorize this app by visiting this url:', authUrl);
const rl = readline.createInterface({
input: process.stdin,
output: process.stdout,
});
rl.question('Enter the code from that page here: ', (code) => {
rl.close();
oAuth2Client.getToken(code, (err, token) => {
if (err) return console.error('Error while trying to retrieve access token', err);
oAuth2Client.setCredentials(token);
// Store the token to disk for later program executions
fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
if (err) return console.error(err);
console.log('Token stored to', TOKEN_PATH);
});
callback(oAuth2Client);
});
});
}
function main(auth) {
const sheets = google.sheets({ version: 'v4', auth });
sheets.spreadsheets.values.update({
spreadsheetId: <<sheet_id>>,
range: 'Class Data!B2:C3',
valueInputOption: "USER_ENTERED",
resource: {
values: [
["hoge", "hogehoge"],
["fuga", "fugafuga"]
]
}
}, (err, result) => {
if (err) {
// Handle error
console.log(err);
} else {
console.log('%d cells updated.', result.updatedCells);
}
});
}Worauf ich im obigen Code besonders möchte, dass Sie achten:
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets']; Googles offizielle Anleitung oben gibt an:
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];was ein Scope nur mit Leseberechtigung ist. In diesem Zustand erhalten Sie Berechtigungsfehler wie unten vorgestellt, also wenn Sie Schreib-/Aktualisierungsarbeiten durchführen, müssen Sie dies wie oben ändern.
Beachten Sie, dass der Scope auch auth/spreadsheets hinzugefügt haben muss, wie in den obigen Schritten. Außerdem könnte es sicher sein, den OAuth-Schlüssel nach der Änderung des Scopes neu auszustellen.
Im obigen Code übergeben wir ein mehrdimensionales Array [[“hoge”, “hogehoge”],[“fuga”, “fugafuga”]] an die 2x2 vier Zellen von “B2:C3” im “Class Data”-Sheet und aktualisieren jede Zelle auf diese Werte.
Durch Ausführen des obigen Codes mit node wird ein Spreadsheet wie (1) unten zu (2) unten aktualisiert.
(Vor der Änderung)
(Nach der Änderung)
Fehler: Error: Insufficient Permission (googleapis)
Unten werde ich häufige Fehler vorstellen.
Der folgende Fehler kann bei der Ausführung mit googleapis auftreten:
Error: Insufficient PermissionDieser Fehler tritt hauptsächlich auf, wenn die Scope-Hinzufügung und -Konfiguration wie oben nicht korrekt durchgeführt werden oder wenn die an die SCOPES-Konstante im Code übergebene Berechtigung (oAuth2Client.generateAuthUrl -> scope) unangemessen ist.
Laut Stack Overflow ist es eine gute Möglichkeit zur Überprüfung, da die Scope-URL in der URL während der OAuth-URL-Authentifizierung enthalten ist, dies als endgültige Überprüfung zu verwenden.
Visiting https://www.googleapis.com/oauth2/v1/tokeninfo?access_token=xxxxxx is a good way to verify [curl - Why is Google Calendar API (oauth2) responding with 'Insufficient Permission'? - Stack Overflow](https://stackoverflow.com/questions/16970917/why-is-google-calendar-api-oauth2-responding-with-insufficient-permission)Referenzen:
python - Google API (Sheets) API Error code 403. Insufficient Permission: Request had insufficient authentication scopes - Stack Overflow curl - Why is Google Calendar API (oauth2) responding with ‘Insufficient Permission’? - Stack Overflow javascript - Google Cloud OAuth2 scopes not updating - Stack Overflow
Referenzen
Unten werde ich andere Referenzen für die Verwendung von Google Sheets API in node.js vorstellen.
Sheets API Samples:
Basic Writing | Sheets API | Google Developers
Sheets API Anleitung (node.js, javascript, python, php, java, ruby etc.):
Reading & Writing Cell Values | Sheets API | Google Developers
Node.js-Referenz in googleapis:
Method: spreadsheets.values.get | Sheets API | Google Developers googleapis documentation
Scopes in Google Sheets API:
Authorize Requests | Sheets API | Google Developers
Für die Verwendung von Sheets API von curl usw.:
Basic Reading | Sheets API | Google Developers Basic Writing | Sheets API | Google Developers
Verwendung von node-google-spreadsheet
In der obigen Einführung haben wir googleapis verwendet, offiziell von Google als Sheets API-Bibliothek für node.js bereitgestellt, aber es gibt andere Drittanbieter-Bibliotheken. Eine davon ist node-google-spreadsheet.
Github:
theoephraim/node-google-spreadsheet: Google Sheets API (v4) wrapper for Node.js
Ich habe node-google-spreadsheet ein wenig berührt, bevor ich googleapis verwendete, aber während ich lesen konnte, bin ich beim Schreiben gestolpert.
Da ich nicht viel Zeit habe, bin ich zur Verwendung von googleapis übergegangen, aber unten werde ich die Umgebungseinrichtung und das API-Hinzufügungsverfahren für die Verwendung von node-google-spreadsheet, Referenzen für node-google-spreadsheet und ein Beispielprogrammbeispiel zum Lesen von Sheets vorstellen.
Erstellen Sie zunächst ein Verzeichnis und package.json mit npm init und installieren Sie node-google-spreadsheet.
npm i google-spreadsheetBeachten Sie, dass die node.js- und Bibliotheksabhängigkeitsversionen meiner Umgebung wie folgt sind:
> node -v
v13.10.1{
"name": "tmp_dev",
"version": "1.0.0",
"description": "",
"main": "tmp_dev.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": "",
"license": "ISC",
"dependencies": {
"google-spreadsheet": "^3.2.0",
"googleapis": "^39.2.0",
}
}Erstellen Sie ein Dienstkonto. Speichern Sie die JSON-Datei.
Unten ist ein Beispielprogramm.
Schreiben Sie das Folgende entsprechend um und führen Sie es aus:
sheet_id .. Spreadsheet-ID; Beispiel: 1jMCd0flts.. client_email .. client_email-Eigenschaft der gespeicherten JSON-Datei private_key .. private_key-Eigenschaft der gespeicherten JSON-Datei
const { GoogleSpreadsheet } = require('google-spreadsheet');
const doc = new GoogleSpreadsheet('<<sheet_id>>');
let rows = [];
(async function(){
await doc.useServiceAccountAuth({
client_email: <<client_email>>,
private_key: <<private_key>>,
});
await doc.loadInfo();
const sheet = doc.sheetsByIndex[0];
rows = await sheet.getRows();
console.log(rows);
})();
Referenzen:
Authentication Overview GoogleSpreadsheetRow GoogleSpreadsheetCell