Unable to create a new spreadsheet with the Google

问题:

I'm using the googleapis package to create a new Google Spreadsheet and it fails. The same request works when I use the web-based tools in the Google API documentation.

Google.prototype.createSheet = function(filename, callback) {
    var services = google.sheets('v4');
    services.spreadsheets.create({
      properties : {title:filename},
      auth       : this.auth
    }, function(err,response) {
      if( err ) {
        console.log('Error : unable to create file, ' + err);
        return;
      } else {
        console.dir(response);
      }
    });
}

The result,

Error : unable to create file, Error: Invalid JSON payload received. Unknown name "properties": Cannot bind query parameter. Field 'properties' could not be found in request message.

I've also tried using a property name of "resource" instead of "properties" because I found that in other sheets endpoints. That also didn't work but resulted in both a different error message but also a different API request when I debug the googleapis code.

Error : unable to create file, Error: Invalid JSON payload received. Unknown name "title" at 'spreadsheet': Cannot find field.

I've also tried creating the file using the Drive API without success.

回答1:

Wow. Turns out the answer is a hybrid of my two experiments. Encapsulating the properties inside of a resource block.

Google.prototype.createSheet = function(filename, callback) {
    var services = google.sheets('v4');
    services.spreadsheets.create({
      resource : {properties:{title:filename}},
      auth       : this.auth
    }, function(err,response) {
      if( err ) {
        console.log('Error : unable to create file, ' + err);
        return;
      } else {
        console.dir(response);
      }
    });
}

I don't see anything in the Google API documentation that indicates that this is the right way to send the request so this isn't very comforting even though it's effective.

回答2:

I recommend using JWT/OAuth2.0 with a service account you can setup through Google Cloud Platform like so:

const {google} = require('googleapis');
let sheets = google.sheets('v4');

const serviceAccount = { INSERT_JSON_HERE  };

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

  return new Promise((resolve, reject) => {
        sheets.spreadsheets.create({
            auth: client,
        }, (err, obj) => {
        if (err) {
            console.log(err);
            reject(err);
        } else {
            console.log(`New Spreadsheet ID: ${obj.data.spreadsheetId}`);
            resolve(obj);
        }
    });