CODEWITHSUNDEEP
javascriptnode.jsgoogle-apigoogle-sheets-api
CAG2
CAG2

Reputation: 411

In Node.JS, how would I "shift" up an entire range of values with the Google Sheets API?

I have a Google Spreadsheet which is read & written from by a Node.JS app.

In one scenario, I need to shift an entire range up (move A3:D up by one), but as I am extremely new to the Google Sheets API, I have failed to do so. I have thought about using these methods:

a) Copy all values in a range and paste it up by one

Using sheets.spreadsheet.values.get, I would get all the values in A3:D then save it as an array using toLocaleString(). Then, I would clear all the values in that range. Finally, I will put that data in a range A2:D.

Problem: When setting the data, would I use an array or would I simply use response.value.data or even simply response.value? How would I pass my array to sheets.spreadsheet.values.set as a parameter?

b) Insert a row below all the values

I would use the method found in this post: Shift Cell Range in Google Sheets

Problem: This seems like a good method, but I have a feeling it will cause strange behaviour. I have a 'title' row in my spreadsheet (with no actual data) and I am worried that doing this will override that. Additionally, what is the best method to get the row number where I would actually insert the new row?


I would appreciate some guidance on how to solve this problem. Thank you!

EDIT 1

I have managed to scrape together this code. I am trying to make a CutPasteRequst, but I cannot figure out the syntax and how I would make this work.

var spreadsheetId = 'My sheet ID';

sheets.spreadsheets.values.batchUpdate({
    auth: client, // 'client' is already authorised
    spreadsheetId: spreadsheetId,

    requestBody: {
        source: 'Words to display!A3:D',
        destination: 'Words to display!A2',
        pasteType: 'pasteValues.PASTE_VALUES'
    }
},
function(err, response) {
    if (err) {
        console.log('API error: ' + err);
    }
});

API errors:

2019-09-18T05:33:03.209240+00:00 app[web.1]: The API returned an error: Error: 

Invalid JSON payload received. Unknown name "source": Cannot find field.
Invalid JSON payload received. Unknown name "destination": Cannot find field.
Invalid JSON payload received. Unknown name "paste_type": Cannot find field.

Upvotes: 1

Views: 652

Answers (1)

Tanaike
Tanaike

Reputation: 201553

  • You want to achieve the following situation using googleapis with Node.js.

    • From

      1   Alpha   Beta    Gamma   Delta
2   a1      b1      c1      d1
3   a2      b2      c2      d2
4   a3      b3      c3      d3
5   a4      b4      c4      d4
6   a5      b5      c5      d5

    • From

      1   Alpha   Beta    Gamma   Delta
2   a2      b2      c2      d2
3   a3      b3      c3      d3
4   a4      b4      c4      d4
5   a5      b5      c5      d5

  • You have already been able to put and get values for Spreadsheet using Sheets API.

If my understanding is correct, how about this sample script.

Patten 1:

In this pattern, the cutPaste request with the method of spreadsheets.batchUpdate is used. The values of "A3:D" are copied to "A2:D".

Sample script:

const spreadsheetId = "###";  // Please set Spreadsheet ID.
const sheetId = ###;  // Please set sheet ID.

sheets.spreadsheets.batchUpdate(
  {
    auth: client,
    spreadsheetId: spreadsheetId,
    requestBody: {
      requests: [
        {
          cutPaste: {
            source: {
              sheetId: sheetId,
              startRowIndex: 2,
              startColumnIndex: 0,
              endColumnIndex: 4
            },
            destination: { sheetId: sheetId, rowIndex: 1 },
            pasteType: "PASTE_NORMAL"
          }
        }
      ]
    }
  },
  function(err, response) {
    if (err) {
        console.log('API error: ' + err);
    }
});

Patten 2:

In this pattern, the deleteDimension request with the method of spreadsheets.batchUpdate is used. The row 2 is deleted.

Sample script:

const spreadsheetId = "###";  // Please set Spreadsheet ID.
const sheetId = ###;  // Please set sheet ID.

sheets.spreadsheets.batchUpdate(
  {
    auth: client,
    spreadsheetId: spreadsheetId,
    requestBody: {
      requests: [
        {
          deleteDimension: {
            range: {
              sheetId: sheetId,
              startIndex: 1,
              endIndex: 2,
              dimension: "ROWS"
            }
          }
        },
        {
          appendDimension: {
            sheetId: sheetId,
            dimension: "ROWS",
            length: 1
          }
        }
      ]
    }
  },
  function(err, response) {
    if (err) {
        console.log('API error: ' + err);
    }
});
  • In this sample script, after one row was deleted, new row is added using the appendDimension request.

Note:

  • At the method of spreadsheets.batchUpdate, please use GridRange which is not a1Notation.
  • When you use this script, if an error occurs, please use the latest version of googleapis. Now the latest version is 43.0.0.

References:

Upvotes: 1

Related Questions