How to use google-spreadsheet - 9 common examples

function getHouseEvents(response) {
  // fetches data stored in the Google Spreadsheet
  var sheet = new GoogleSpreadsheet(env.get(`HOUSE_EVENT_SPREADSHEET_ID_2018`));

  // line breaks are essential for the private key.
  // if reading this private key from env var this extra replace step is a MUST
  sheet.useServiceAccountAuth({
    "client_email": env.get(`GOOGLE_API_CLIENT_EMAIL_2018`),
    "private_key": env.get(`GOOGLE_API_PRIVATE_KEY_2018`).replace(/\\n/g, `\n`)
  }, (err) => {
    if (err) {
      console.log(`[Error] ${err}`);
      response.status(500).json(err);
    }
    // GoogleSpreadsheet.getRows(worksheet_id, callback)
    sheet.getRows(1, (sheetErr, rows) => {
      if (sheetErr) {
        console.log("[Error] ", sheetErr);
        response.status(500).json(sheetErr);

app.post('/add-fringe-event', limiter, (req, res) => {
  var SPREADSHEET_ID = env.get(`FRINGE_EVENT_SPREADSHEET_ID_2018`);
  var sheet = new GoogleSpreadsheet(SPREADSHEET_ID);
  var fringeEvent = req.body;

  // line breaks are essential for the private key.
  // if reading this private key from env var this extra replace step is a MUST
  sheet.useServiceAccountAuth({
    "client_email": env.get(`GOOGLE_API_CLIENT_EMAIL_2018`),
    "private_key": env.get(`GOOGLE_API_PRIVATE_KEY_2018`).replace(/\\n/g, `\n`)
  }, (err) => {
    if (err) {
      console.log(`[Error] ${err}`);
      res.status(500).json(err);
    }

    sheet.addRow(1, fringeEvent, (addRowErr) => {
      if (addRowErr) {
        console.log(`[addRowErr]`, addRowErr);

function getFringeEvents(response) {
  // fetches data stored in the Fringe Events Google Spreadsheet
  var sheet = new GoogleSpreadsheet(env.get(`FRINGE_EVENT_SPREADSHEET_ID_2018`));

  // line breaks are essential for the private key.
  // if reading this private key from env var this extra replace step is a MUST
  sheet.useServiceAccountAuth({
    "client_email": env.get(`GOOGLE_API_CLIENT_EMAIL_2018`),
    "private_key": env.get(`GOOGLE_API_PRIVATE_KEY_2018`).replace(/\\n/g, `\n`)
  }, (err) => {
    if (err) {
      console.log(`[Error] ${err}`);
      response.status(500).json(err);
    }
    // GoogleSpreadsheet.getRows(worksheet_id, callback)
    sheet.getRows(1, (sheetErr, rows) => {
      if (sheetErr) {
        console.log("[Error] ", sheetErr);
        response.status(500).json(sheetErr);

async function appendSheet(sheetName: string, rows: ({ date: string; name: string } & unknown)[]) {
  const doc = new GoogleSpreadsheet(process.env.SPREADSHEET_ID)
  try {
    await promisify(doc.useServiceAccountAuth)(creds)
    const info = await promisify(doc.getInfo)()
    log.info(`Loaded doc: ${info.title} by ${info.author.email}`)
    const sheet = info.worksheets.filter((s: { title: string; rowCount: number } & unknown) => s.title === sheetName)[0]
    log.info(`${sheetName} sheet: ${sheet.title} ${sheet.rowCount}x${sheet.colCount}`)
    if (sheet.title === sheetName) {
      const inserted = rows.map(insert_row => promisify(sheet.addRow)(insert_row))
      log.info(`Wrote ${inserted.length} rows.`)
      return await Promise.all(inserted)
    }
    log.info('Wrong sheet!')
  } catch (error) {
    throw error
  }
}

export function extractSheets({spreadsheetKey, sheetsToExtract, credentials = {}, formatCell = a => a} = {}, cb) {
    var spreadSheet = new GoogleSpreadsheet(spreadsheetKey);

    if (!credentials) {
      return doExtractSheets(spreadSheet, sheetsToExtract, formatCell, cb);
    }

    spreadSheet.useServiceAccountAuth(credentials, function(err){
        if (err) {
            return cb(err);
        }
        doExtractSheets(spreadSheet, sheetsToExtract, formatCell, cb)
    });
};

createSpreadsheet(options = {}) {
    if (typeof options === 'string') {
      options = { sheetId: String(options) }
    }

    const { sheetId = this.options.sheetId || this.provider.sheetId } = options

    return new GoogleSpreadsheet(sheetId)
  }

async function appendSheet(sheetName: string, rows: ({ date: string; name: string } & unknown)[]) {
  const doc = new GoogleSpreadsheet(process.env.SPREADSHEET_ID)
  await promisify(doc.useServiceAccountAuth)(creds)
  const info = await promisify(doc.getInfo)()
  log.info(`Loaded doc: ${info.title} by ${info.author.email}`)
  const sheet = info.worksheets.filter((s: { title: string; rowCount: number } & unknown) => s.title === sheetName)[0]
  log.info(`${sheetName} sheet: ${sheet.title} ${sheet.rowCount}x${sheet.colCount}`)
  if (sheet.title === sheetName) {
    const inserted = rows.map(insert_row => promisify(sheet.addRow)(insert_row))
    log.info(`Wrote ${inserted.length} rows.`)
    return Promise.all(inserted)
  }
  log.info('Wrong sheet!')
}

export const getScores = async (id: string) => {
  const document = new googleSpreadsheet(id);

  await setAuth(document);

  const sheet: any = await getWorksheet(document);
  const rows: any = await getRows(sheet);

  const scores: any = rows.map((row: any) => {
    const github =
      row['github'] ||
      row['githublogin'] ||
      row['githublink'] ||
      row['githuburl'] ||
      row['githubusername'] ||
      '';

    if (!github) {

async function appendSheet(sheetName: string, rows: MetricsSheetRow[]) {
  const doc = new GoogleSpreadsheet(process.env.SPREADSHEET_ID)
  await promisify(doc.useServiceAccountAuth)(creds)
  const info = await promisify(doc.getInfo)()
  log.info(`Loaded doc: ${info.title} by ${info.author.email}`)
  const sheet = info.worksheets.filter((s: { title: string; rowCount: number } & unknown) => s.title === sheetName)[0]
  log.info(`${sheetName} sheet: ${sheet.title} ${sheet.rowCount}x${sheet.colCount}`)
  if (sheet.title === sheetName) {
    const inserted = rows.map(insert_row => promisify(sheet.addRow)(insert_row))
    log.info(`Wrote ${inserted.length} rows.`)
    return Promise.all(inserted)
  }
  log.info('Wrong sheet!')
}