Search code examples
javascriptnode.jsnode-modulesgoogle-sheets-api

google-spreadsheet node.js. Copying and pasting cells in the range

there! I have two ranges of cells BM4:BM29 and BN4:BN29. I need to copy the cell values from the first range and paste into the other range. The cells in the first range have formulas that add up the numbers in the other cells in the table. I need to copy this number and paste into the other range.

I'm using: https://www.npmjs.com/package/google-spreadsheet/v/4.1.0

To authenticate, I use:

const { GoogleSpreadsheet } = require('google-spreadsheet');
const { JWT } = require('google-auth-library');
require("dotenv").config();

const serviceAccountAuth = new JWT({
    email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
    key: process.env.GOOGLE_PRIVATE_KEY,
    scopes: [
        'https://www.googleapis.com/auth/spreadsheets',
        'https://www.googleapis.com/auth/drive'
    ],
});
const doc = new GoogleSpreadsheet(process.env.SPREADSHEET_TABLE_ID, serviceAccountAuth);

module.exports = { doc };

I have tried many different methods but they have not produced any results.

await doc.loadInfo();
const sheet = doc.sheetsById[SOME_SHEET_ID];
await sheet.loadCells();

sheet.loadCellsInRange()
sheet.copyPaste()
sheet.getCellByA1()

https://i.sstatic.net/Ms4hP.png https://i.sstatic.net/kZdC3.png

Solution

  • From your following reply,

    I added screenshots from the table with formulas. I need the values from column BM to be copied to column BN

    In this case, how about the following modification? In this modification, the values of column "BM" are copied to column "BN" using the batchUpdate method. Unfortunately, I couldn't find the batchUpdate method in google-spreadsheet. So, I directly request Sheets API using https module. The access token and Spreadsheet ID are retrieved from doc in your script, respectively. Please modify as follows.

    From:

    await doc.loadInfo();
const sheet = doc.sheetsById[SOME_SHEET_ID];
await sheet.loadCells();

sheet.loadCellsInRange()
sheet.copyPaste()
sheet.getCellByA1()

    To:

    Please set your sheet ID.

    const https = require("https");

const sheetId = 12345678; // Please set your sheet ID.

const srcStartColumnIndex = 64; // Column "BM"
const dstStartColumnIndex = 65; // Column "BN"

const url = `https://sheets.googleapis.com/v4/spreadsheets/${doc.spreadsheetId}:batchUpdate`;
const options = {
  method: "POST",
  headers: {
    "Content-Type": "application/json",
    "Authorization": "Bearer " + doc.jwtClient.gtoken.rawToken.access_token,
  },
};
const postData = {
  requests: [
    {
      copyPaste: {
        source: { sheetId, startRowIndex: 0, startColumnIndex: srcStartColumnIndex, endColumnIndex: srcStartColumnIndex + 1 },
        destination: { sheetId, startRowIndex: 0, startColumnIndex: dstStartColumnIndex, endColumnIndex: dstStartColumnIndex + 1 },
        pasteType: "PASTE_VALUES",
      },
    },
  ],
};
const req = https.request(url, options, (res) => {
  const chunks = [];
  res
    .on("data", (chunk) => chunks.push(Buffer.from(chunk)))
    .on("end", () => console.log(Buffer.concat(chunks).toString()));
});
req.write(JSON.stringify(postData));
req.on("error", (err) => console.log(err));
req.end();

    • By this modification, when this script is run, the values of column "BM" are copied to column "BN" using the batchUpdate method by one API call. In this case, only values are copied.

    • If you want to copy "BM4:BM29" to "BN4:BN29", please modify source: { sheetId, startRowIndex: 0, startColumnIndex: srcStartColumnIndex, endColumnIndex: srcStartColumnIndex + 1 }, and destination: { sheetId, startRowIndex: 0, startColumnIndex: dstStartColumnIndex, endColumnIndex: dstStartColumnIndex + 1 }, as follows.

      • source: { sheetId, startRowIndex: 3, endRowIndex: 29, startColumnIndex: srcStartColumnIndex, endColumnIndex: srcStartColumnIndex + 1 },
      • destination: { sheetId, startRowIndex: 3, endRowIndex: 29, startColumnIndex: dstStartColumnIndex, endColumnIndex: dstStartColumnIndex + 1 },

    References: