0

I am developing an Add-on that aims to keep the sales on a marketplace in synced with Google Sheets. There is a first time sync that writes the last year orders into the sheet and that happens from within the add on execution, all good there.

Now I receive notifications in a cloud function when there is a new sale, and I want to add that information to the spreadsheet that the Add-on has been given permission to, but I don't know how to authenticate the request. I have tried with an editor service account and also creating a key for the main app engine account and nothing works.

Everything is part of the same google project, and the users will be anyone that install the add on and gives access to it, some help or direction will be highly appreciated.

Scopes the add on has permissions:

"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/spreadsheets.currentonly",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/userinfo.email",
"https://www.googleapis.com/auth/script.external_request",
"https://www.googleapis.com/auth/script.locale",
"https://www.googleapis.com/auth/script.scriptapp",
"https://www.googleapis.com/auth/script.container.ui"

Example usage:

import { google } from 'googleapis';
import serviceAccount from '../service-account.json';

const sheets = google.sheets('v4');

const jwtClient = new google.auth.JWT({
  email: serviceAccount.client_email,
  key: serviceAccount.private_key,
  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});

async function searchMetadata(spreadsheetId, params = {}) {
  await jwtClient.authorize();
  const response = await sheets.spreadsheets.developerMetadata.search({
    auth: jwtClient,
    spreadsheetId,
    requestBody: {
      dataFilters: [
        {
          developerMetadataLookup: params
        }
      ]
    }
  })
  return response.data;
}

Thanks!

Leandro Zubrezki
  • 1,150
  • 9
  • 12
  • Can you provide your script so far? – Rafa Guillermo Apr 01 '21 at 12:47
  • Also, having both `https://www.googleapis.com/auth/spreadsheets.currentonly` and `https://www.googleapis.com/auth/spreadsheets` is redundant as the second one gives full read/write access to all Spreadsheets anyway. – Rafa Guillermo Apr 01 '21 at 12:47
  • @RafaGuillermo added the example usage, as I mentioned I've tried with different service accounts with different roles and nothing have worked so far. – Leandro Zubrezki Apr 01 '21 at 17:42
  • Hang on - this isn’t Google Apps Script - how are you doing this from an add-on? – Rafa Guillermo Apr 01 '21 at 18:50
  • That’s the point, once a user gives permissions, I want to be able to modify the spreadsheet from outside the app script environment, specifically from cloud functions. – Leandro Zubrezki Apr 03 '21 at 04:28
  • I understand. Couple of questions: 1) Is using a service account required? If so, check out authenticating with a JWT in [this answer](https://stackoverflow.com/a/61932919/11551468). 2) Have you set up domain-wide delegation so that the service account can access the sheet on your behalf? 3) Have you got any code so far to use the Sheets API? – Rafa Guillermo Apr 03 '21 at 10:05
  • 1) I have tried also without a service account but it doesn't work either, as in that case will take the Cloud Function role. The example code is the same as in the answer. 2) I tried with and without domain wide delegation in the service account, same. 3) Yes, all the interaction from within the apps script environment works fine. Thanks for helping anyway!, I have also posted a question in the google apps script group and nothing for now. – Leandro Zubrezki Apr 05 '21 at 18:51
  • So, what does the code you have so far do, compared to what it's meant to do? – Rafa Guillermo Apr 06 '21 at 07:54
  • The idea was to keep the spreadsheet in sync with the orders, I get notifications when an order is created so I wanted to append the new ones and updates directly to the spreadsheet from cloud functions. For now I settle with a time trigger that runs every hour and checks the database for new and updated orders. But the main goal is to make that real time. – Leandro Zubrezki Apr 06 '21 at 15:09

0 Answers0