Write Form Data from my Chrome Extension to Google Sheets

Question

Updated with snippets and today's progress:

I am writing a Chrome Extension that is essentially a popup with a form, and I would like to write data entered into that form into Google Sheets. Currently, my extension consists of a manifest.json and a popup script, and a background script.

manifest.json (relevant pieces):

"background": {
    "scripts": ["background.js"],
    "persistent": false
  },
  "content_scripts": [{ "js": ["content.js"], "matches": ["<all_urls>"] }],
  "permissions": [
    "tabs",
    "storage",
    "<all_urls>",
    "identity",
    "https://*.googleapis.com/*"
  ]

popup.js (note: this is an extension to track MS symptoms)

const app = {

  symptoms: [],

  init: function () {
    //cache some element references
    let formEl = document.getElementById("symptoms-form");

    let fatigue = document.getElementById("fatigue");
    let tingling = document.getElementById("tingling");
    let weakness = document.getElementById("weakness");
    let vision = document.getElementById("vision");
    let dizzy = document.getElementById("dizzy");
    let cognition = document.getElementById("cognition");
    let depression = document.getElementById("depression");
    let balance = document.getElementById("balance");

    //upon submit, update symptoms obj and send to background
    formEl.addEventListener("submit", ev => {
      ev.preventDefault();
      console.log('button click')
      this.symptoms.push({fatigue: fatigue.value})
      this.symptoms.push({tingling: tingling.value})
      this.symptoms.push({weakness: weakness.value})
      this.symptoms.push({vision: vision.value})
      this.symptoms.push({dizzy: dizzy.value})
      this.symptoms.push({cognition: cognition.value})
      this.symptoms.push({depression: depression.value})
      this.symptoms.push({balance: balance.value})

      // chrome.runtime.sendMessage({fn: 'getSymptoms'}, function(response) {
      //   console.log('popup got response', response)
      // })

      chrome.runtime.sendMessage({fn: 'setSymptoms', symptoms: this.symptoms})
    });

  }
}

document.addEventListener('DOMContentLoaded', () => {
  app.init();
})

background.js - note: my current workaround is to load the data into Firebase, which you will see below:

console.log("Background running");

const background = {
  symptoms: [],

  init: function() {
    //listen for any messages and route them to functions
    chrome.runtime.onMessage.addListener((request, sender, sendResponse) => {
      if (request.fn in background) {
        background[request.fn](request, sender, sendResponse);
      }
      const jsonObj = {}
      jsonObj['symptoms'] = request.symptoms
      console.log("message received", jsonObj);


      this.postSymptoms(jsonObj)
    });
  },

  postSymptoms: function(msg) {
    const xhr = new XMLHttpRequest();

    xhr.open("POST", "https://ms-mysymptoms-1541705437963.firebaseio.com/symptoms.json", true);
    xhr.setRequestHeader("Content-Type", "application/json;charset=UTF-8");
    xhr.send(msg);
  }
};

background.init();

I have set up a new project in the Google Developers console, enabled the Google Sheets API, and set up my credentials and API token. I tested in the Google API explorer that the authentication is set up properly and I can, indeed, write a row to my sheet. This is great news!

I am blocked right now on how to do this (write the data), directly from my Chrome extension. So far, I have saved all my credentials, set up a config file, and wrote my append method in a separate file locally.

sheets.js:

const {authorize, google} = require('./config')
const fs = require('fs')

const spreadsheetId = '---removed for this post--'
const append = (range, values) => {
  fs.readFile('client_secret.json', (err, content) => {
    if (err) return console.log('Error loading client secret file:', err);
    // Authorize a client with credentials, then call the Google Sheets API.
    authorize(JSON.parse(content), (auth) => {
      const sheets = google.sheets({
        version: 'v4',
        auth
      });
      const valueInputOption = 'USER_ENTERED';
      const resource = {
        values
      };
      sheets.spreadsheets.values.append({
        spreadsheetId,
        range,
        valueInputOption,
        resource
      }, (err, result) => {
        if (err) {
          console.log(err);
        } else {
          console.log("Success!");
        }
      });
    });
  });
}

// module.exports = {
//     append
// };

When I try to integrate this code into my popup script, however, I encounter an error because in order to reference that config data and that append method, I have to use require in my popup script. Since the popup script is running in the browser, I can't use require (without webpack, that is).

I'm sure I'm going about this all wrong, so I could use a push in the right direction as to how to authenticate and append to Sheets from the browser if my configuration and authentication are stored in local files on my computer.

Solutions I've considered:

1 - spin up a REST API, post the data from the form to that endpoint, and have it act as a proxy to the Google Sheets API - this is not ideal.

2 - use webpack so that I can use require in my popup file

What would be the recommended way to do this? How should I integrate authentication and working with the Google Sheet into this extension?

Answer 1

Careful! If you want to append data, the ? query parameter comes after the :append.

fetch(`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}:append?valueInputOption=${valueInputOption}`, init)

Answer 2

Writing to a spreadsheet with Google's API is a PUT not a POST.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update

I had success with this using chrome.identity.getAuthToken, then running a fetch with the following:

    chrome.identity.getAuthToken({interactive: true}, function(token) {
        var params = {
            'values': [
                ['Row 1 Col A','Row 1 Col B'],
                ['Row 2 Col A','Row 2 Col B'],
            ]
        };
        let init = {
            method: 'PUT',
            async: true,
            body: JSON.stringify(params),
            headers: {
                Authorization: 'Bearer ' + token,
                Content-Type': 'application/json'
            },
            contentType: 'json',
        };
        fetch('https://sheets.googleapis.com/v4/spreadsheets/***YOUR SHEET ID****/values/****YOUR RANGE*****?valueInputOption=USER_ENTERED&key=***YOUR API KEY***', init)
            .then((response) => response.json())
            .then(function(data) {
                //console.log(data);
                //Returns spreadsheet ID, update tange, cols and rows
            });
        })
   });

That's all in the background script, where I've put Row 1 Col A etc as the values, that'll be the first cell of your range.

Hope that helps.