How to access or open Spreadsheet which I have created using google sheets API ?

[ravics09]

I am using googleapis to create a google sheet. my working code

const { google } = require("googleapis");
const keys = require("./credentials.json");

var spreadsheetId = null;
var gsapi = null;
const title = "SheetExample1!";

createConnection = () => {
  return new Promise((resolve, reject) => {
    const client = new google.auth.JWT(
      keys.client_email,
      null,
      keys.private_key,
      ["https://www.googleapis.com/auth/spreadsheets"]
    );

    client.authorize((err, res) => {
      if (err) {
        reject(err);
      } else {
        gsapi = google.sheets({
          version: "v4",
          auth: client,
        });
        console.log("Connection Created Successfully");
        resolve(res);
      }
    });
  });
};

createSheet = () => {
  return new Promise((resolve, reject) => {
    const resource = {
      properties: {
        title,
      },
    };
    gsapi.spreadsheets.create(
      {
        resource,
        fields: "spreadsheetId",
      },
      (err, response) => {
        if (err) {
          reject(err);
        } else {
          console.log(
            "Sheet Created with spreadsheetId============",
            response.data.spreadsheetId
          );
          resolve(response.data);
        }
      }
    );
  });
};

functionHelper = async () => {
  await createConnection();
  await createSheet();
};

functionHelper();

In response, I am getting spreadsheetId. But I have no idea where all these sheets are stored and how I can open a spreadsheet using the generated spreadsheet Id.

I checked I can't see these files inside https://docs.google.com/spreadsheets/u/0/

Any suggestion?

[oshliaer]

to @ravics09

Hi Ravi!

It seems you're using JWT auth for a Service Account. Are you using the service account privately or is it a domain-wide delegation service account?

If it's a custom service account the the file was created on its drive space. You can share the file to yourself

import { google, drive_v3 } from 'googleapis';
import path from 'path';

import env from '../env.json';

(async () => {
  const auth = new google.auth.JWT({
    // subject: env.DOMAIN_EFFECTIVE_USER_EMAIL,
    keyFile: path.join(__dirname, './..', env.CUSTOM_SERVICE_ACCOUNT),
    scopes: ['https://www.googleapis.com/auth/drive'],
  });

  const drive: drive_v3.Drive = google.drive({
    version: 'v3',
    auth: auth,
  });

  var fileId = '1BPs-7w-spreadsheetId_0vZutmP7Q5XcET3lMA';
  var permission = {
    type: 'user',
    role: 'writer',
    emailAddress: env.MY_EMAIL,
  };
  try {
    const createResponse = await drive.permissions.create({
      fileId,
      requestBody: permission,
    });

    console.log(createResponse.data);
  } catch (err) {
    console.log(err);
  }
})();

So if you delegated the service account for your domain you can create the sheet from a domain user

import { google, sheets_v4 } from 'googleapis';
import path from 'path';

import env from '../env.json';

(async () => {
  const auth = new google.auth.JWT({
    subject: env.DOMAIN_EFFECTIVE_USER_EMAIL,
    keyFile: path.join(__dirname, './..', env.DOMAIN_SERVICE_ACCOUNT),
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
  });

  const sheets: sheets_v4.Sheets = google.sheets({
    version: 'v4',
    auth: auth,
  });

  try {
    const createResponse = await sheets.spreadsheets.create({
      requestBody: {
        properties: {
          title: 'My sheet',
        },
      },
    });

    console.log(createResponse.data);
  } catch (err) {
    console.log(err);
  }
})();