Unable to write to Google Spreadsheet from actions on google with dialogflow

Question

I am trying to write to a Google Spreadsheet from an Actions on Google app with Dialogflow. I am able to read from the sheet using my app. The spreadsheet is given a public edit access.

I am using below code in the fulfillment webhook to read and write to spreadsheet. I have my SPREADSHEET_ID and SPREADSHEET_API_KEY:

function welcome(agent) {
    const tabName = 'Sheet1';
    const startCell = 'B2';
    const endCell = 'D';

    appendDataToSpreadsheet(tabName, startCell, endCell);
    agent.add(`Appended`);
}

function appendDataToSpreadsheet(tabName, startCell, endCell) {
  const sheets = google.sheets({version: 'v4', auth: SPREADSHEET_API_KEY});
  return sheets.spreadsheets.values.append({
    auth: auth,
    spreadsheetId: SPREADSHEET_ID,
    range: `${tabName}!${startCell}:${endCell}`,
    valueInputOption: "USER_ENTERED",
    resource: {
      values: [ ["5", "Anis", "8", "React"], ["6", "Paul", "1", "Python"] ]
    }
  }, (err, response) => {
    if (err) {
        console.log('The API returned an error: ' + err);
        return;
      } else {
        console.log("Appended");
    }
  });
}

This code does not append the data to spreadsheet and in my logs I can see following error:

The API returned an error:
Error: Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.

The app is asking for authentication credential. Any pointers on how to go about this will be helpful.

Writing requires OAuth2, as stated by the error message, and explained in related questions. — tehhowch, Oct 09 '18 at 11:48

score 1 · Accepted Answer · edited Apr 09 '19 at 20:00

I would recommend authenticating by setting up a service account that uses JWT-OAuth2.0:

const spreadsheetId = 'INSERT_HERE'; 

const serviceAccount = { INSERT_HERE };     // {"type": "service_account",...

// Set up Google Calendar Service account credentials
const serviceAccountAuth = new google.auth.JWT({
  email: serviceAccount.client_email,
  key: serviceAccount.private_key,
  scopes: 'https://www.googleapis.com/auth/spreadsheet'
});

You can do this by going to the Google Cloud Platform link inside your Dialogflow project settings or https://console.cloud.google.com/home/dashboard?project=INSERT_AGENT_NAME.

From there go to APIs & Services > Credentials > Create Credentials > Service Account Key > Project/Owner.

score 0 · Answer 2 · answered Oct 09 '18 at 17:08

In order to write to a sheet, you need to get authorization to do so, even for sheets that are publicly writable, because writing requires a user account to annotated who made the change. While an API Key is sufficient to read the sheet (since there is no audit log), you need an account to actually write.

If you're doing this just through an Action, you can maintain the auth token and refresh token for a single user and keep it hidden. Better, however, would be to use Google Sign In for Assistant and an OAuth2 flow to keep track of exactly who edits the spreadsheet.

Unable to write to Google Spreadsheet from actions on google with dialogflow

2 Answers2