Web_Advance
  • 本書簡介
  • Node.js 部分
    • Node 版本管理
    • 使用NPM
      • Yarn
    • 開始Node
    • Worker Thread
    • REPL
    • TCP
    • path
    • Cluster and Child_process
    • assert (自訂拋出的錯誤)
    • Stream(流)
    • util (工具類)
    • EventEmitter
    • fs 文件操作
    • Buffer
      • Binary Diff
      • 查看 Binary 檔案內容
    • Process (進程)
    • 錯誤處理
  • OS
  • Async Hook
  • TCP
  • HTTP
    • 有關爬蟲
    • HTTP/2
    • HTTP Protocal
  • HTTPS
    • HTTPS 流程
    • SSL pinning
    • HTTPS 封包解密
    • 建立自簽發 HTTPS 證書
    • 幫網站加上HTTPS
    • HTTPS原理
  • Crypto加密
  • 有關繼承
  • JS 基本
    • JavaScript 迴圈與異步處理
  • 使用 Express
    • 上傳檔案
    • 圖片伺服器
    • 簡單範例
  • 使用 Nest.js
  • 使用MongoDB
    • 設置帳戶登入權限
    • Mongoose 框架
    • 進階Mongo
    • 基本環境操作
    • MongoDB Sharding
  • 使用MySQL
    • Schema 架構設計
    • SQL 語法
    • SQL Procedure
    • Node.js 操作 MySQL
    • 使用 Sequelize
      • DB Migration
      • function
  • 使用PostgreSQL
    • 常見問題
    • replica
    • 基本指令
    • 使用Node.js操控pg
    • SQL 基礎
  • 使用TypeORM
  • RethinkDB
  • CSS 深度探討
    • Width, Height
  • React
    • 第三方組件
      • Formik
    • styled component
    • propTypes
    • React webpack 部署
    • React util
    • 寫component並且publish
    • create-react-app
    • Context API
    • i18n
    • Server side render
    • Next.js教學
    • Higher order component 與 Recompose
    • component 間 互相存取
    • React hook
  • React router
    • 自己寫一個Router
  • Redux
    • Redux Toolkit
    • 小技巧
    • Redux sagas
    • compose
  • React Native
    • adb
    • InApp Billing
    • Icon
    • SVG
    • Firebase
      • Phone Auth
    • 自動化測試
    • Splash screen
    • Websocket
    • Googla OAuth
      • iOS
      • Android
    • Facebook OAuth
      • iOS
      • Android
    • IOS
    • 第三方組件
      • Auth Code Input
      • Country Code Picker
      • onboarding screen
      • Toast
    • ESlint
    • Push notification
    • Android 上架步驟
    • Expo
    • router
      • react-navigation套件
    • 原生組件
      • RefreshControl
      • Modal
      • Alert
      • button
      • KeyboardAvoidingView
      • Drawer
      • Image
    • 限制螢幕垂直與水平
    • NativeBase UI
    • Debug
    • 常見問題
    • Network
    • 硬體操作
      • 隱藏鍵盤
      • 地理位置
      • 相機與圖片庫存取
    • Async Storage
    • Animation
    • Admob
  • JS 模組化
  • 使用 Webpack
  • 使用 Babel
  • JWT Token
  • ES6 ES7 ES8
    • Array method
    • ES8 Async
    • ES6 Proxy
    • ES6 Object
    • ES6 Arrow function
    • ES6 Promise
    • ES6 Symbol
    • ES6 Generator
    • ES6 Set,Map
    • ES6 Class
  • 模板引擎
    • Mustache
    • Handlebars.js
    • EJS
  • ESLint
  • 部屬到OpenShift
  • OpenStack
  • OAuth
    • Twitter OAuth
    • Google authenticator
    • facebook oauth
      • facebook like ,share
    • google oauth
  • Redis
  • 做一個簡單的markdown editor
  • Websocket
    • WebSocket 相關 Protocol
  • Sublime 安裝套件
  • Google api
    • Cloud Run
    • speech api
    • place autocomplete
    • Geocode
    • Map
      • React map
    • vision api
    • Google-recaptcha
    • Google sheet
  • Instagram API
  • Markdown 與 code pretty js
  • HTML5
    • IntersectionObserver
    • HTML5 audio
    • HTML5 Video 與 WebRTC
      • WebRTC 進階
      • WebEX API
    • HTML5 IndexedDB
  • Google Cloud Platform (GCP)
    • Cloud Storage
    • Cloud storage 串接 Cloud CDN
  • Vim 編輯器
  • 使用nginx
    • config
  • Unix 實用指令
    • 新 VPS 安裝流程
    • Ubuntu 22 安裝
    • Shell Script 教學
  • Git 實用指令
    • Git hook
    • 加上 SSH-key 到 GitHub
    • GPG簽名
  • SSH 實用指令
  • 有關Fetch與axios與跨域請求
  • 圖片上傳相關
    • imgur API
  • JS 格式轉換
  • js trick
  • AWS
    • AWS EBS
    • AWS HTTPS 憑證
    • AWS Cloudfront、ELB、ACL
    • AWS Athena
    • AWS CloudWatch、SNS
    • AWS RDS
    • AWS lambda
      • 範例
      • 加上權限控管
    • AWS S3
    • AWS DynamoDB
      • 結合Lambda
    • 快速把 EC2 串上 AWS Cloudfront CDN
    • AWS 證照相關
  • 有關日期Date
  • VS code 編輯器
    • VSCode 外掛 Plugin
  • CI with Gitlab&Jenkins
  • API 測試
    • Postman
      • 設置 Postman 環境變數
    • API Blueprint
    • swagger
      • 註解寫在Code內生成swagger UI
  • Javascript 實用Lib
  • 遠端寫程式
  • Quicktime錄影注意事項
  • Web開發進階Bug
  • Web壓力測試
  • LineBot
  • PM2部署
  • i18n
  • VPN
  • Protocol Buffers
  • Docker教學
    • LXC LXD
    • Docker Compose
    • Docker 原理
    • Docker 指令
  • E2E Testing
    • Cypress
    • PlayWright
    • Puppeteer 與其他 UI 測試工具
  • Unit Test (Jest & enzyme)
    • React Testing Library
    • mocha
  • Cassandra
    • cluster
  • Distribute Web
    • Dat project
    • IPFS project
  • Cluster and Child_process
  • 打包應用程式
  • Java
    • 使用gradle結合docker
  • Debug 頁面
  • Proxy
  • Chrome extension
  • 消息系統
    • RabbitMQ
  • 金流串接
    • Paypal
    • spgateway智富通
    • Stripe 串接
  • 有關Log
  • 設定 feature flag
  • Azure
    • Face API
    • Image Analyze API
    • Azure Serverless
    • Cosmos DB
      • 使用 SDK
      • 以 RESTful 操作 DB
      • 一致性策略與 DB replicate
  • NodeBB 筆記
  • 瀏覽器快取與緩存(Etag, If-None-Match)
  • 瀏覽器快取與緩存(Expires, Last-modified, Cache-Control)
  • Node.js 第三方模組
    • OpenCV
  • Kubernetes
    • 本地測試 MiniKube
  • Ngrok 使用
  • Telegram MiniAPP 開發
  • Firebase 教學
  • 演算法筆記
  • 圖表
    • Echart
    • TradingView 圖表
    • D3
    • 熱力圖 heatmap
  • 後端緩存 Cache
  • 資料一致性
  • Web 安全機制
    • Cookie 與 LocalStorage
  • Vue
    • Element UI
    • Devtool
    • Vuex
    • Vue router
  • 相關網路協議
    • 網路 IP 基礎
    • Google Search 技巧
    • 網路診斷工具
    • IP
    • DNS
  • GitLab 與 Drone
  • SMTP、POP、IMAP
    • SendGrid
  • IPC
  • 串流服務
    • Twilio
    • Agora
  • 其他資源
  • GraphQL
  • Typescript
  • UI 相關資源
  • FFmpeg
  • Unity 遊戲開發筆記
  • Influx DB
  • Windows 相關
  • DALL·E 3
  • Coap
  • Slack API
  • 資訊安全
    • 破解 ZIP 密碼
Powered by GitBook
On this page
  • Google Sheet
  • 寫入範例(記得改scope)
  • 讀取A sheet並寫入 B sheet範例

Was this helpful?

Edit on GitHub
  1. Google api

Google sheet

PreviousGoogle-recaptchaNextInstagram API

Last updated 5 years ago

Was this helpful?

Google Sheet

Google試算表 API

以下為官方Node.js範例

1.點選Enable The Sheet API然後下載憑證

2.創建新的google sheet 並更改 code 的 spreadsheetId3.npm install googleapis

程式範例:

const fs = require('fs');
const readline = require('readline');
const { google } = require('googleapis');

// If modifying these scopes, delete token.json.
var SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
const TOKEN_PATH = 'token.json';

// Load client secrets from a local file.
fs.readFile('credentials.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), listMajors);
});

/**
 * Create an OAuth2 client with the given credentials, and then execute the
 * given callback function.
 * @param {Object} credentials The authorization client credentials.
 * @param {function} callback The callback to call with the authorized client.
 */
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);
    });
}

/**
 * Get and store new token after prompting for user authorization, and then
 * execute the given callback with the authorized OAuth2 client.
 * @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
 * @param {getEventsCallback} callback The callback for the authorized client.
 */
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);
        });
    });
}

/**
 * Prints the names and majors of students in a sample spreadsheet:
 * @see https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
 * @param {google.auth.OAuth2} auth The authenticated Google OAuth client.
 */
function listMajors(auth) {
    const sheets = google.sheets({ version: 'v4', auth });
    sheets.spreadsheets.values.get({
        spreadsheetId: '1aCA49m9XIKbX3NZk4FA5WDvSfOQEAbAfFQ_-MSQGdAo',
        range: ['Sheet1!A1:B2'],
    }, (err, res) => {
        if (err) return console.log('The API returned an error: ' + err);
        const rows = res.data.values;
        if (rows.length) {
            rows.map((row) => {
                console.log(row);
            });
        } else {
            console.log('No data found.');
        }
    });
}

第一次執行後在console會產生網址 之後到那個網址會有hash 把它貼到console 之後本地電腦會產生token,供下次使用

Range可參考

Sheet1!A1:B2 refers to the first two cells in the top two rows of Sheet1.
Sheet1!A:A refers to all the cells in the first column of Sheet1.
Sheet1!1:2 refers to the all the cells in the first two rows of Sheet1.
Sheet1!A5:A refers to all the cells of the first column of Sheet 1, from row 5 onward.
A1:B2 refers to the first two cells in the top two rows of the first visible sheet.
Sheet1 refers to all the cells in Sheet1.

SCOPE( read/write )

如出現

   [ { message: 'Request had insufficient authentication scopes.',
       domain: 'global',
       reason: 'forbidden' } ] }

更改SCOPE記得把電腦內的./credential 憑證刪除剛產生的

value | describe
---- | ---
https://www.googleapis.com/auth/spreadsheets.readonly | Allows read-only access to the user's sheets and their properties.
https://www.googleapis.com/auth/spreadsheets |    Allows read/write access to the user's sheets and their properties.
https://www.googleapis.com/auth/drive.readonly |    Allows read-only access to the user's file metadata and file content.
https://www.googleapis.com/auth/drive.file |    Per-file access to files created or opened by the app.
https://www.googleapis.com/auth/drive |    Full, permissive scope to access all of a user's files. Request this scope only when it is strictly necessary.

寫入範例(記得改scope)

function listMajors(auth) {
    var sheets = google.sheets('v4');
    var values = [
        [
            12123
        ],
        // Additional rows ...
    ];
    var body = {
        values: values
    };
    sheets.spreadsheets.values.update({
        auth: auth,
        spreadsheetId: '1aCA49m9XIKbX3NZk4FA5WDvSfOQEAbAfFQ_-MSQGdAo',
        range: ['sheet1'],
        valueInputOption: 'RAW',
        resource: body
    }, function (err, result) {
        if (err) {
            // Handle error
            console.log(err);
        } else {
            console.log('%d cells updated.', result.updatedCells);
        }
    });
}

讀取A sheet並寫入 B sheet範例

const fs = require('fs');
const readline = require('readline');
const { google } = require('googleapis');

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const spreadsheetId = '1aCA49m9XIKbX3NZk4FA5WDvSfOQEAbAfFQ_-MSQGdAo';
const TOKEN_PATH = 'token.json';

// Load client secrets from a local file.
fs.readFile('credentials.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), doAction);
});

/**
* Create an OAuth2 client with the given credentials, and then execute the
* given callback function.
* @param {Object} credentials The authorization client credentials.
* @param {function} callback The callback to call with the authorized client.
*/
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);
    });
}

/**
* Get and store new token after prompting for user authorization, and then
* execute the given callback with the authorized OAuth2 client.
* @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
* @param {getEventsCallback} callback The callback for the authorized client.
*/
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 readSheet2(auth) {
    return new Promise((resolve, reject) => {
        const sheets = google.sheets({ version: 'v4', auth });
        sheets.spreadsheets.values.get({
            spreadsheetId,
            range: ['Sheet2'],
        }, (err, res) => {
            if (err) return console.log('The API returned an error: ' + err);
            const rows = res.data.values;
            if (rows.length) {
                rows.shift(); // 移除欄位名稱
                resolve(rows);
            } else {
                reject('No data found.');
            }
        });

    })
}


function updateSheet1(auth, values) {
    var sheets = google.sheets('v4');
    var body = {
        values
    };
    sheets.spreadsheets.values.append({
        auth,
        spreadsheetId,
        range: ['sheet1'],
        valueInputOption: 'RAW',
        resource: body
    }, function (err, result) {
        if (err) {
            // Handle error
            console.log(err);
        } else {
            console.log('%d cells updated.', result.updatedCells);
        }
    });
}

async function doAction(auth) {
  const sheet2Value = await readSheet2(auth);
  updateSheet1(auth, sheet2Value);
}

https://github.com/google/google-auth-library-nodejs/issues/168
https://developers.google.com/sheets/api/quickstart/nodejs