Create a google sheet from a template

I recently had to work with Google Sheets in NodeJS. It was a fun challenge for the day and surprisingly was not too much work. Here is the final “demo” we gave to the client in its entirety, afterwards I’ll break it down section by section.

import * as googleAuth from 'googleapis';  
import { google } from 'googleapis';  
const creds = require('./credentials.json');  
  
void (async () => {  
    const auth = new googleAuth.Auth.GoogleAuth({  
        scopes: [  
            'https://www.googleapis.com/auth/spreadsheets',  
            'https://www.googleapis.com/auth/drive',  
            'https://www.googleapis.com/auth/drive.file',  
        ],  
        credentials: creds,  
    });

    google.options({ auth: await auth.getClient() });  
  
    google.options({ auth: await auth.getClient() });  
    const sheets = google.sheets('v4');  
    const drive = google.drive('v3');  
  
    const copyRes = await drive.files.copy({  
        fileId: 'templateSheetId',  
        requestBody: {  
            name: 'Copy of sheet',  
        },  
    });  
    const sheetId = copyRes.data.id;  
      
    await sheets.spreadsheets.values.batchUpdate({  
        spreadsheetId: sheetId,  
        requestBody: {  
            data: [  
                { range: "A1", values: [[9999]] },  
                { range: "A2", values: [[123456]] },  
            ],  
            valueInputOption: 'USER_ENTERED',  
        },  
    });  
    
    await drive.permissions.create({  
        requestBody: {  
            type: 'user',  
            role: 'writer',  
            emailAddress: 'example@example.com',  
        },  
        fileId: sheetId,  
        fields: 'id',  
    });  
)}()

Firstly we import the required packages, ironically, figuring out which package to import was difficult as the documentation is surprisingly not very good

import * as googleAuth from 'googleapis';  
import { google } from 'googleapis';  
const creds = require('./credentials.json');

Next, we Authorize with google using a credential file generated in the google cloud dashboard. We need to ensure we provide all the scopes we’ll be using

const auth = new googleAuth.Auth.GoogleAuth({  
    scopes: [  
        'https://www.googleapis.com/auth/spreadsheets',  
        'https://www.googleapis.com/auth/drive',  
        'https://www.googleapis.com/auth/drive.file',  
    ],  
    credentials: creds,  
});

google.options({ auth: await auth.getClient() });

We then initialise the sheets and drive objects which allow us to interact with the respective services

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

We can then use drive to create a copy of the template sheet

const copyRes = await drive.files.copy({  
    fileId: 'templateSheetId',  
    requestBody: {  
        name: 'Copy of sheet',  
    },  
});  
const sheetId = copyRes.data.id;

Using the returned sheetId we can then use sheets to mutate the data

await sheets.spreadsheets.values.batchUpdate({  
    spreadsheetId: sheetId,  
    requestBody: {  
        data: [  
            { range: "A1", values: [[9999]] },  
            { range: "A2", values: [[123456]] },  
        ],  
        valueInputOption: 'USER_ENTERED',  
    },  
});

Finally to ensure the user we’re creating this sheet for can access it, we use drive to create a permission for them.

await drive.permissions.create({  
    requestBody: {  
        type: 'user',  
        role: 'writer',  
        emailAddress: 'example@example.com',  
    },  
    fileId: sheetId,  
    fields: 'id',  
});

The user example@example.com will shortly receive an email informing them that the file has been shared with them.

pfy.ch