CODEWITHSUNDEEP
javagoogle-sheets-api
lakshman
lakshman

Reputation: 2741

How to duplicate a sheet in google spreadsheet api

I have used below code to do that.

BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();

DuplicateSheetRequest requestBody = new DuplicateSheetRequest();
requestBody.setNewSheetName("test");
requestBody.setSourceSheetId(sheetId);

Sheets sheetsService = createSheetsService();

batchUpdateSpreadsheetRequest.set("duplicateSheet", requestBody);
Sheets.Spreadsheets.BatchUpdate request =  sheetsService.spreadsheets().
batchUpdate(spreadsheetId,batchUpdateSpreadsheetRequest);
BatchUpdateSpreadsheetResponse response = request.execute();

When I execute this, I get below error.

Exception in thread "main" com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "message" : "Invalid JSON payload received. Unknown name \"duplicate_sheet\": Cannot find field.",
    "reason" : "badRequest"
  } ],
  "message" : "Invalid JSON payload received. Unknown name \"duplicate_sheet\": Cannot find field.",
  "status" : "INVALID_ARGUMENT"
}

what am I doing wrong here? What is the correct way to create duplicate of sheet?

Upvotes: 2

Views: 5209

Answers (3)

Brian
Brian

Reputation: 869

Here is a C# (.net 6) version:

public void Duplicate(int SourceSheetId, string NewSheetName) {

        BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
        batchUpdateSpreadsheetRequest.Requests = new List<Request>();

        batchUpdateSpreadsheetRequest.Requests.Add(new Request() {
            DuplicateSheet = new DuplicateSheetRequest() {
                NewSheetName = NewSheetName,
                SourceSheetId = SourceSheetId
            },
        });

        var req = Service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SheetID);  //public SheetsService Service; property of parent class
        BatchUpdateSpreadsheetResponse response = req.Execute();
    }

Upvotes: 0

Vaxo Basilidze
Vaxo Basilidze

Reputation: 1057

Here is the PHP version of creating a sheet duplicate in case you need:

//Assuming you already have a $spreadsheet
$spreadsheetId = $spreadsheet->spreadsheetId;
$body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
    'requests' => array(
        'duplicateSheet' => array(
            'sourceSheetId' => 0, //Source sheet id goes here as an integer
            'insertSheetIndex' => 1, //Position where the new sheet should be inserted
            'newSheetName' => 'test' //Set new name if you want
        )
    )
));
$spreadsheet = $service->spreadsheets->batchUpdate($spreadsheetId, $body);

Upvotes: 2

lakshman
lakshman

Reputation: 2741

I have managed to overcome this problem by following something similar from google documentation. Below is the code I used.

BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new 
BatchUpdateSpreadsheetRequest();

List<Request> requests = new ArrayList<>();

DuplicateSheetRequest requestBody = new DuplicateSheetRequest();
requestBody.setNewSheetName("test");
requestBody.setSourceSheetId(sheetId);

requests.add(new Request().setDuplicateSheet(requestBody));

Sheets sheetsService = createSheetsService();

batchUpdateSpreadsheetRequest.setRequests(requests);
Sheets.Spreadsheets.BatchUpdate request =
            sheetsService.spreadsheets().batchUpdate(spreadsheetId, batchUpdateSpreadsheetRequest);

BatchUpdateSpreadsheetResponse response = request.execute();

Upvotes: 3

Related Questions