How to batch-copy range with Google Sheets Api

Question

My code is:

function Test_Copy() {
  var spreadsheetId = "MY_FILE_ID";
  var sheetId = "SHEET_ID";

  var rangeFrom = Sheets.newGridRange();
  rangeFrom.sheetId = sheetId;
  rangeFrom.startRowIndex = 0;
  rangeFrom.endRowIndex = 1;  
  rangeFrom.startColumnIndex = 0;
  rangeFrom.endColumnIndex = 1;

  var rangeTo = Sheets.newGridRange();
  rangeTo.sheetId = sheetId;
  rangeTo.startRowIndex = 1;
  rangeTo.endRowIndex = 2;  
  rangeTo.startColumnIndex = 1;
  rangeTo.endColumnIndex = 2;  


  var request = Sheets.newCopyPasteRequest();
  request.destination =  rangeTo      
  request.source =  rangeFrom
  request.pasteType = "PASTE_NORMAL";
  request.pasteOrientation = "NORMAL";

  var resource = Sheets.newBatchUpdateSpreadsheetRequest();
  resource.requests = [request];

  Sheets.Spreadsheets.batchUpdate(resource , spreadsheetId);

}

When I run it, I get the error:

API call to sheets.spreadsheets.batchUpdate failed with error: Invalid JSON payload received. Unknown name "pasteType"

I use Google-Sheets-Api, (here's how I enabled it)

The answer below is correct. The error could easily be avoided if you had directly used object literal notation: {requests:[requestObj]} — TheMaster
Good to see you are still around, Max. Your VK group has been a bit dormant lately ) — Anton Dementiev

Anton Dementiev Anton Dementiev · Accepted Answer · 2019-12-05T14:00:58

I don't have much experience with the Sheets API but this seems to work as it logs the response data:

var copyPasteRequest = Sheets.newCopyPasteRequest();
  copyPasteRequest.destination =  rangeTo;    
  copyPasteRequest.source =  rangeFrom;
  copyPasteRequest.pasteType = "PASTE_NORMAL";
  copyPasteRequest.pasteOrientation = "NORMAL";

  var resource = Sheets.newBatchUpdateSpreadsheetRequest();

  //Create request object
  var request = Sheets.newRequest();
  request.copyPaste = copyPasteRequest;
  resource.requests = [request];

  var res = Sheets.Spreadsheets.batchUpdate(resource , spreadsheetId);
  Logger.log(res.replies);
  Logger.log(res.spreadsheetId);

I think the issue was that you were trying to set your CopyPasteRequest object as a property of BatchUpdateRequest object but apparently it needs a Request object as an intermediary.

{
  "requests": [
    {
      object (Request)
    }
  ],
  "includeSpreadsheetInResponse": boolean,
  "responseRanges": [
    string
  ],
  "responseIncludeGridData": boolean
}

I could be wrong though.

How to batch-copy range with Google Sheets Api

1 Answers