Copy / paste selection from google sheets into google docs via docs and sheets API?

user_6894116 0
Viewed 109

I want to transfer a selection from a google sheet document to a google docs document via my node.js backend service. I was hoping that the google docs API supports this feature, but I can't find the exact steps needed.

To visualize what I want to do, I have taken a simple screen capture in the web UI, that shows exactly what needs to happen - just not handheld, but automated in my backend service: enter image description here

It is my understanding that the google web apps almost uses the exact same API as is exposed to developers, so I was hoping that someone here can tell me what exact requests are made for this to happen.

This question is somewhat similar: Copy selection from Google Sheets into a Google Doc (using REST API?) but it 4+ years old, and haven't gotten much attention, and a lot have happened to the APIs in the meantime. Furthermore I am not interested in using app scripts, which the answer suggests doing.

google-docs-api node.js google-sheets-api
1 Answers

I believe your goal is as follows.

  • You want to copy the values from Google Spreadsheet and paste them to Google Document as a table.
  • You want to achieve this using googleapis for Node.js.
  • You have already been able to use Sheets API and Docs API with Node.js.

If my understanding is correct, how about the following sample script?

I had the same situation as you. But unfortunately, at that time, I thought that this process might be a bit complicated. So I created a library of Node.js for achieving this. This sample script uses the library. Ref

Usage:

1. Install a library.

$ npm install --save-dev gdoctableapp

or

$ npm install --global gdoctableapp

2. Prepare script.

In this sample script, please use your auth in your script. This auth is the same as Quickstart for Node.js. Ref But in this case, please set the scopes of https://www.googleapis.com/auth/spreadsheets.readonly and https://www.googleapis.com/auth/documents.

const gdoctableapp = require("gdoctableapp"); // This is the library.

const spreadsheetId = "###"; // Please set your Spreadsheet ID.
const documentId = "###"; // Please set your Document ID.

const sheets = google.sheets({ version: "v4", auth });
const { data } = await sheets.spreadsheets.values.get({ spreadsheetId, range: "'Sheet1'!A1:C3" }).catch((e) => console.log(e));
const values = data.values;
gdoctableapp.CreateTable(
  {
    auth,
    documentId,
    values,
    rows: values.length,
    columns: values[0].length,
    append: true,
  },
  (err, res) => {
    if (err) {
      console.log(err);
      return;
    }
    console.log(res);
  }
);
  • When this script is run, the values are retrieved from the cells 'Sheet1'!A1:C3 and put to the Google Document as a table. In this case, the table is appended to the Google Document.

Node:

  • This sample script supposes that you have already been able to use Sheets API and Docs API, and retrieve the access token for using the APIs. So please be careful about this.

References:

user_71086530
Related