Google sheets cut and paste script

Question

all.

Seen a lot of cut and paste Google Sheets scripts where all of them fundamentally fail due to the difference between copy from source + paste in destination + delete source and cut source + paste in destination. The difference being that the pasted content references the same cells as the original did. Copying in the same sheet results in copying the formula inside the cell and eventually increasing its non-fixed cell references' rows and columns. Pasting copied content on another sheet strikes another major difference between the aforementioned two operations - the pasted cells' formulas reference values from the destination sheet rather than from the source one.

What is the proper way to do a cut & paste of a Range in Google Sheets as a script? I saw only this as a viable answer but it is a network request, not Google Sheets script. It is probably possible to send a request from Sheets script but presumably there should be a more integrated way to do a cut & paste. Probably the HTTP request way is meant for external applications.

Alessandro Alessandro · Accepted Answer · 2020-05-01T09:05:44

On Google Apps Script you can use the Sheets API through the Advanced Services. To enable it, click on Resources>Advanced Google services...>Google Sheets API from the script editor menu.

Once you get it activated you can use the same endpoints listed here.

Sample

  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheetId_1 = ss.getSheetByName("source").getSheetId();
  let sheetId_2 = ss.getSheetByName("destination).getSheetId();
  // Using the Sheets API to create the request body
  let request = Sheets.newCutPasteRequest();
  let source = {
    startColumnIndex : 0,
    endColumnIndex : 4,
    startRowIndex : 0,
    endRowIndex : 10,
    sheetId : sheetId_1
  }
  let destination = {
    columnIndex : 5,
    rowIndex : 0,
    sheetId : sheetId_2
  }
  request.source = source;
  request.destination = destination;
  request.pasteType = "PASTE_NORMAL";
  // Calling the Sheets API "batchUpdate" endpoint
  Sheets.Spreadsheets.batchUpdate({"requests": [{"cutPaste" : request }]}, ss.getId());

Reference

Advanced Google services

Google sheets cut and paste script

1 Answers

Sample

Reference