Insert a row in the middle of existing data

Question

I'm trying to insert an empty row in the middle of the spreadsheet using node.js and Google Sheets API v4. I've been researching everywhere for past few days, but couldn't find any documentation about it.

Google Spreadsheet Documentation only talks about .append(), which appends a row at the end of the spreadsheet. This is not quite what I want haha.

Is there even a way to insert an empty row in the middle of the data??

---

So.. Here is what I edited so far.

function newRow() {
  return new Promise((resolve, reject) => {
    return getAuthorizedClient().then(client => {
      const sheets = google.sheets({
        version: 'v4',
        auth: client
      });
      var requests = [];

      requests.push({
        insertDimension: {
          range: {
            sheetId: MY_SHEET_ID,
            dimension: 'row',
            startIndex: 50,
            endIndex: 1
          },
          inheritFromBefore: false
        }
      });
      var batchUpdateRequest = { requests: requests };

      sheets.spreadsheets.values.batchUpdate({
        spreadsheetId: CONFIG_SHEET_ID,
        resource: batchUpdateRequest
      });
    });
  });
}

getAuthorizedClient is a function I made for auth. Anyway, the problem I get here is

Error: Invalid JSON payload received. Unknown name "requests": Cannot find field.

I'm not too sure what I'm doing wrong - I'm following straight from the Google API documentation.

Answer 1

Per the Sheets API reference, to insert rows you need to send an InsertDimensionRequest with a valid DimensionRange property.

Indeed, this part of your code is correct:

{
  insertDimension: {
    range: {
      sheetId: MY_SHEET_ID,
      dimension: 'ROWS',
      startIndex: 50,
      endIndex: 1
    },
    inheritFromBefore: false // Use start (true) or end (false) index to set defaults.
  }
}

(Though I believe you want to have your end index as 51 and not 1. That would then insert a row at the 50th index, where Row 1 is index 0.)

From the error message

Error: Invalid JSON payload received. Unknown name "requests": Cannot find field.

we can determine that your particular batchUpdate request has incorrect. syntax. Note that there are multiple classes in the Sheets API that implement a batchUpdate method: spreadsheets.batchUpdate and spreadsheets.values.batchUpdate. Only for the batchUpdate method of the spreadsheets resource is an array of input Requests a valid parameter - batch updates of the values resource requires a data parameter (i.e., the values to be written to the targeted cells).

Thus, the fix is to change the class used to construct the batchUpdate request:

function newRow(sheets, config, options) {
  var newRowRequest = {
    insertDimension: {
      range: {
        sheetId: options.sheetId,
        dimension: 'ROWS',
        startIndex: options.start,
        endIndex: options.start + options.howMany
      },
      inheritFromBefore: options.inheritBefore
    }
  };

  var batchUpdateRequest = {
    requests: [newRowRequest]
    // includeSpreadsheetInResponse: ...,
    // responseRanges: [...],
    // responseIncludeGridData: ...
  };
  return sheets.spreadsheets.batchUpdate({
    spreadsheetId: config.spreadsheetId,
    resource: batchUpdateRequest
  }); // Returns AxiosPromise
}

• Node.js API reference for Sheets REST API.
  • spreadsheets.batchUpdate
  • spreadsheets.values.batchUpdate

Answer 2

Google Apps Script Documentation is pretty good for this sort of thing.

https://developers.google.com/apps-script/reference/spreadsheet/sheet

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

// This inserts a row after the first row position.
sheet.insertRowAfter(1);