How to read a spreadsheet using Google Sheets API + Javascript

Question

There's a nice solution to updating Google spreadsheets using the Drive REST API in this question.

It overwrites the current contents. I'd like to read in the current contents, modify them, then update.

How do I read in the current spreadsheet?

Answer 1

This works (assuming your credentials are in order and you are authorized):

function getFileInfo(fileId, callback) {
    try {
        gapi.client.drive.files.get({
            fileId: fileId,
            fields: "appProperties,capabilities,contentHints,createdTime,description,explicitlyTrashed,fileExtension,folderColorRgb,fullFileExtension,headRevisionId,iconLink,id,imageMediaMetadata,isAppAuthorized,kind,lastModifyingUser,md5Checksum,mimeType,modifiedByMeTime,modifiedTime,name,originalFilename,ownedByMe,owners,parents,permissions,properties,quotaBytesUsed,shared,sharedWithMeTime,sharingUser,size,spaces,starred,thumbnailLink,trashed,version,videoMediaMetadata,viewedByMe,viewedByMeTime,viewersCanCopyContent,webContentLink,webViewLink,writersCanShare",
        }).then(function(response) {
            info = JSON.parse(response.body);
            callback(info);
        })
    } catch (err) {
        console.log('Something bad happened: ' + err);
    }
}

It returns all the known metadata fields (as of this time). You'll want to trim back the list to those you need. But it does not return the actual spreadsheet contents. For that, you'll need to use the export api:

function getSpreadsheet(fileId, callback) {
    var spreadsheet;
    try {
        gapi.client.drive.files.export({
            fileId: fileId,
            mimeType: 'text/csv'
        }).then(function(response) {
            spreadsheet = Papa.parse(response.body).data;
            callback(spreadsheet);
        })
    } catch (err) {
        console.log('Something bad happened: ' + err);
    }
}

The data is return as a csv string. We use Papa.parse to turn it into a 2D array.