CODEWITHSUNDEEP
c#google-api
Olga
Olga

Reputation: 11

How to duplicate a worksheet using Google API?

I'm trying to create a worksheet in a spreadsheet which is a copy of the existing one. Via Google Docs user interface it is done by 'Duplicate' function of the context menu of a worksheet. Is it possible to copy the whole sheet at once and insert it to the other one? This code:

 public void CopyLastTab(SpreadsheetEntry spreadsheet, string newTabName)
   {
        WorksheetEntry tabToCopy = GetLastTab(spreadsheet);
        tabToCopy.Title.Text = newTabName;
        newTab = service.Insert(GetAllWorksheets(spreadsheet), tabToCopy);           
   }

adds only a new empty worksheet. Copying cell by cell is too long and the matter is I need conditional formatting used on the previous sheet too. Please help.

Upvotes: 1

Views: 381

Answers (1)

Brian
Brian

Reputation: 869

Old question but just spent several hours trying to find an answer. The following works for C# .net 6:

Note this assumes you have an instance of Google SheetsService (Google.APIs.Sheets.v4) that has already been authenticated. I'll post some authentication code below.

public void Duplicate(int SourceSheetId, string NewSheetName) {

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

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

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


private void _authenticate()
{
    UserCredential credential;

    using (var stream = new FileStream("credentials.json", FileMode.Open, FileAccess.ReadWrite))
    {
        string credPath = "token.json";

        var cancelToken = new CancellationToken();
        credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
            GoogleClientSecrets.Load(stream).Secrets,
            _scopes,
            YOUR-EMAIL-HERE,
            cancelToken,
            new FileDataStore(credPath, true)).Result;

        //credential.RevokeTokenAsync(cancelToken); //UNCOMMENT TO LOG OUT
    }
    Service = new SheetsService(new BaseClientService.Initializer()
    {
        HttpClientInitializer = credential,
        ApplicationName = "",
    });

}

Upvotes: 0

Related Questions