change access permissions of google spreadsheet using google apis

Question

I am trying to change my google spreadsheet's accessibility permission using google API, I have created it using my google service account using google API.

Also can I give it's ownership to some other email address and keep editing access to myself?

Code for generating(I am using node js) :-

const client = new google.auth.JWT(
  keys.client_email,
  null,
  keys.private_key,
  ['https://www.googleapis.com/auth/spreadsheets']
);

client.authorize((err, tokens) => {
  if (err) {
    console.log('sheet auth err', err);
    return;
  }
  console.log('success auth');
});

app.post('/generateSheet', async (req, res, next) => {
  const { userId, sheetName } = req.body;
  console.log(userId, sheetName);
  // return res.redirect('/');
  const integrationName = 'Google_Sheets';

    const data = req.body;
  try {
    // const integrationInfoSnapshot = await db
        //  .doc(`integrationParameters/${integrationName}`)
    //   .get();
    try {
      const gsapi = google.sheets({
        version: 'v4',
        auth: client,
      });
      request_ = {
        resource: {
          properties: {
            title: sheetName
          }
        }
      }
      const response = await gsapi.spreadsheets.create(request_);

      // GIVE SPREADSHEET OWNERSHIP TO SOME OTHER EMAIL ADDRESS AND KEEP EDITING ACCESS TO MY SELF
 
      console.log(response);
    } catch (e) {
      console.log(`Error while creating google sheet for user: ${userId}: `, e);
    }
  } catch (e) {
        console.log(`Error while activating integration for user: "${userId}": `, e);
        res.status(500).send({ message: e.message });
    }
});

It would be a great help if someone can answer. Please Help!!!

Answer 1

In order to give the permissions as the writer using email, you can achieve this using the method of Permissions: create in Drive API. Unfortunately, in the current stage, Sheets API cannot do this.

From your script, for example, when the permissions are given to the created Spreadsheet, the script is as follows.

Modified script:

From:

const client = new google.auth.JWT(
  keys.client_email,
  null,
  keys.private_key,
  ['https://www.googleapis.com/auth/spreadsheets']
);

To:

const client = new google.auth.JWT(
  keys.client_email,
  null,
  keys.private_key,
  ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
);

And, also please modify as follows.

From:

const response = await gsapi.spreadsheets.create(request_);

To:

const response = await gsapi.spreadsheets.create(request_);

// I added below script.
const fileId = response.data.spreadsheetId;
drive = google.drive({ version: "v3", auth: client });
const res = await drive.permissions.create({
  resource: {
    type: "user",
    role: "writer",
    emailAddress: "###",  // Please set the email address you want to give the permission.
  },
  fileId: fileId,
  fields: "id",
});

Reference

• Permissions: create