https://github.com/firebase/functions-samples/tree/master/google-sheet-syncにてno access refresh token is setとエラーが表示される
https://github.com/firebase/functions-samples/tree/master/google-sheet-sync
上記サイトの通り認証を得て、firebase.configで設定を行い,firebase上にdeployを行いました。実際手順は以下の通りです。
実際に行った手順
- githubでダウンロード後、 create an OAuth Client ID でリダイレクト設定
- node.js processファイル内にてGCP project番号の紐づけ(コマンド)
- firebase initで初期化(コマンド)
- firebase addでfirebaseプロジェクトと紐づけ(コマンド)
- firebase functions:config:set googleapi.client_id="YOUR_CLIENT_ID" googleapi.client_secret="YOUR_CLIENT_SECRET" oauther認証IDを設定(コマンド)
- firebase functions:config:set googleapi.sheet_id="YOUR_SPREADSHEET_ID"
スプレッドシートIDを設定(コマンド) - firebase functions:config:set watchedpaths.data_path="THE_DATA_PATH_YOU_WANT"
にて、realtimedb書込み先特定(コマンド) - 下記コードをdeploy (コマンド)
https://{YOUR-PROJET-ID}.firebaseapp.com/authgoogleapi
にて認証⇒OKhttps://{YOUR-PROJET-ID}.{YOUR_PROJET_ID}.firebaseapp.com/testsheetwrite
にて、書込み実施⇒ realtimedbには上手く行くが、スプレッドシート書込みがエラーが出る。
'use strict';
// Sample trigger function that copies new Firebase data to a Google Sheet
const functions = require('firebase-functions');
const admin = require('firebase-admin');
admin.initializeApp();
const {OAuth2Client} = require('google-auth-library');
const {google} = require('googleapis');
// TODO: Use firebase functions:config:set to configure your googleapi object:
// googleapi.client_id = Google API client ID,
// googleapi.client_secret = client secret, and
// googleapi.sheet_id = Google Sheet id (long string in middle of sheet URL)
const CONFIG_CLIENT_ID = functions.config().googleapi.client_id;
const CONFIG_CLIENT_SECRET = functions.config().googleapi.client_secret;
const CONFIG_SHEET_ID = functions.config().googleapi.sheet_id;
// TODO: Use firebase functions:config:set to configure your watchedpaths object:
// watchedpaths.data_path = Firebase path for data to be synced to Google Sheet
const CONFIG_DATA_PATH = functions.config().watchedpaths.data_path;
// The OAuth Callback Redirect.
const FUNCTIONS_REDIRECT = `https://${process.env.GCLOUD_PROJECT}.firebaseapp.com/oauthcallback`;
// setup for authGoogleAPI
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const functionsOauthClient = new OAuth2Client(CONFIG_CLIENT_ID, CONFIG_CLIENT_SECRET,
FUNCTIONS_REDIRECT);
// OAuth token cached locally.
let oauthTokens='client.secret.json';
// visit the URL for this Function to request tokens
exports.authgoogleapi = functions.https.onRequest((req, res) => {
res.set('Cache-Control', 'private, max-age=0, s-maxage=0');
res.redirect(functionsOauthClient.generateAuthUrl({
access_type: 'offline',
scope: SCOPES,
prompt: 'consent',
}));
});
// setup for OauthCallback
const DB_TOKEN_PATH = '/api_tokens';
const tokens=functions.config().googleapi.acess_key;
// after you grant access, you will be redirected to the URL for this Function
// this Function stores the tokens to your Firebase database
exports.oauthcallback = functions.https.onRequest((req, res) => {
res.set('Cache-Control', 'private, max-age=0, s-maxage=0');
const code = req.query.code;
functionsOauthClient.getToken(code, (err, tokens) => {
// Now tokens contains an access_token and an optional refresh_token. Save them.
if (err) {
return res.status(400).send(err);
}
return admin.database().ref(DB_TOKEN_PATH).set(tokens)
.then(() => {
return res.status(200).send('App successfully configured with new Credentials. '
+ 'You can now close this page.');
});
});
});
// trigger function to write to Sheet when new data comes in on CONFIG_DATA_PATH
exports.appendrecordtospreadsheet = functions.database.ref(`${CONFIG_DATA_PATH}/{ITEM}`).onCreate(
(snap) => {
const newRecord = snap.val();
return appendPromise({
spreadsheetId: CONFIG_SHEET_ID,
range: 'A:C',
valueInputOption: 'USER_ENTERED',
insertDataOption: 'INSERT_ROWS',
resource: {
values: [[newRecord.firstColumn, newRecord.secondColumn, newRecord.thirdColumn]],
},
});
});
// accepts an append request, returns a Promise to append it, enriching it with auth
function appendPromise(requestWithoutAuth) {
return new Promise((resolve, reject) => {
return getAuthorizedClient().then((client) => {
const sheets = google.sheets('v4');
const request = requestWithoutAuth;
request.auth = client;
return sheets.spreadsheets.values.append(request, (err, response) => {
if (err) {
console.log(`The API returned an error: ${err}`);
return reject(err);
}
return resolve(response.data);
});
});
});
}
// checks if oauthTokens have been loaded into memory, and if not, retrieves them
function getAuthorizedClient() {
if (oauthTokens) {
return Promise.resolve(functionsOauthClient);
}
return admin.database().ref(DB_TOKEN_PATH).once('value').then((snapshot) => {
oauthTokens = snapshot.val();
functionsOauthClient.setCredentials(oauthTokens);
return functionsOauthClient;
});
}
// HTTPS function to write new data to CONFIG_DATA_PATH, for testing
exports.testsheetwrite = functions.https.onRequest((req, res) => {
const random1 = Math.floor(Math.random() * 100);
const random2 = Math.floor(Math.random() * 100);
const random3 = Math.floor(Math.random() * 100);
const ID = new Date().getUTCMilliseconds();
return admin.database().ref(`${CONFIG_DATA_PATH}/${ID}`).set({
firstColumn: random1,
secondColumn: random2,
thirdColumn: random3,
}).then(() => res.status(200).send(
`Wrote ${random1}, ${random2}, ${random3} to DB, trigger should now update Sheet.`));
});
● // OAuth token cached locally.
let oauthTokens='client.secret.json';
で正しいでしょうか。
● // setup for OauthCallback
const DB_TOKEN_PATH = '/api_tokens';
ですが、firebaseのrealtimedb内の/api_tokens
にpostmanから取得したaccess_token
とrefresh_token
を格納して読み込ませる方法で
正しいでしょうか?
長文で申し訳御座いません。もしお分かりになる方がいらっしゃいましたら教えて頂ければと
思います。宜しくお願い致します。