Receiving 403 error when trying to update a value in a row

[niclake]

I am working with the following spreadsheet: https://docs.google.com/spreadsheets/d/1-1PcHF6xzFKTaTvxnfjm6bVgo4pd5yIr3nbxsbckoFo/edit?usp=sharing

You can view this link, but have no ability to update it. I, via my personal account, am the only person who can.

I've been using this package to assist me in fetching data from this sheet to punt into some local JSON previously, so I know the connection has at least been established. I've also learned that, if you don't at least go through the process of exposing a sheet to "Anyone with the link", it can return errors when you try and access the data.

But now, when I'm attempting to write a value into a row, I receive a 403 The caller does not have permission error, and I'm not quite sure what I'm doing wrong. I've been following the documentation and am now stuck.

Here's my code that I'm working with:

import 'dotenv/config'
import { GoogleSpreadsheet } from 'google-spreadsheet';
import { JWT } from 'google-auth-library';
import 'node-fetch';

// Initialize auth - see https://theoephraim.github.io/node-google-spreadsheet/#/guides/authentication
const serviceAccountAuth = new JWT({
  // env var values here are copied from service account credentials generated by google
  // see "Authentication" section in docs for more info
  email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
  key: process.env.GOOGLE_PRIVATE_KEY,
  scopes: ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive.file'],
})

const books = new GoogleSpreadsheet(process.env.BOOKS_SHEET_ID, serviceAccountAuth)

await books.loadInfo() // loads document properties and worksheets
const booksSheet = books.sheetsByTitle['Books'] // or use `doc.sheetsById[id]` or `doc.sheetsByTitle[title]`
const allBooks = await booksSheet.getRows()

for (var i = 0; i < allBooks.length; i++) {
  if (allBooks[i].get("Pages") != "") { continue; }
  if (allBooks[i].get("Read?") != "X") { continue; }

  const bookInfo = await getBookInfo(
    allBooks[i].get("Title"), 
    allBooks[i].get("Author First"), 
    allBooks[i].get("Author Last")
  );

  // At this point, I know that bookInfo["number_of_pages_median"] has my page count; set it.
  allBooks[i].set("Pages", bookInfo["number_of_pages_median"]); // I've also tried stringifying this
  console.log(allBooks[i].get("Pages")) // returns the correct page count that I've retrieved from OpenLibrary
  await allBooks[i].save(); // <<< This is where it bombs out on me
  break; // Only trying to do this for the first one to test
}

async function getBookInfo(title, authorFirst, authorLast) {
  const formTitle = title.replace(/\s+/g, '+').toLowerCase();
  const formAuthor = (authorFirst + '+' + authorLast).replace(/\s+/g, '+').toLowerCase();
  const request = await fetch(`https://openlibrary.org/search.json?title=${formTitle}&author=${formAuthor}&lang=en&limit=1`);
  const data = await request.json();
  return data["docs"][0];
}

[niclake]

Actually... perhaps I should be using the OAuth method for authentication, vs. the Service Account method? Would that help me out here, since this spreadsheet is something that I own myself, vs. something that's part of an organization?

[niclake]

Ok, I did end up figuring this out.

When you create a Google service account, it gives you this email address that's @ .com. I was working on the assumption that since this was tied to my Google account, everything would Just Work™.

Instead, you need to copy this email address, go to your Google Sheet, share it, and grant this email editor access. The second that's done, bam, you're golden.

Feel free to close.

[theoephraim]

Glad you got it sorted out. It is in the docs, although perhaps could be highlighted more. https://theoephraim.github.io/node-google-spreadsheet/#/guides/authentication?id=service-account