Find the sheet with SpreadSheetId and SheetId C#

Question

I am currently using regex to find the "SpreadSheetId" and the "SheetId" of the google sheet in the url. I can find the file with the "SpreadSheetId" and name of sheet but ... I don't understand why I can't use the "SheetId" to find it. it only works with the exact name of the sheet.

Google API V4 : https://developers.google.com/sheets/api/quickstart/dotnet

How can I find the sheet with the sheetId instead of the name.

            UserCredential credential;

            using (var stream =
                new FileStream("code_secret_client.json", FileMode.Open, FileAccess.Read))
            {
                // The file token.json stores the user's access and refresh tokens, and is created
                // automatically when the authorization flow completes for the first time.
                string credPath = "token.json";
                credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
                    GoogleClientSecrets.Load(stream).Secrets,
                    Scopes,
                    "user",
                    CancellationToken.None,
                    new FileDataStore(credPath, true)).Result;
                Console.WriteLine("Credential file saved to: " + credPath);
            }

            // Create Google Sheets API service.
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });

        // Define request parameters.
        //1HtsjLDHo9oaNqeSPKt8YRCEKg5lfa9ONzytJm_6thS4
        //find regex
        Regex RxSpreadSheetId = new Regex(@"/spreadsheets/d/([a-zA-Z0-9-_]+)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
        Regex RxSheetId = new Regex(@"[#&]gid=([0-9]+)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
        // 
        string URL = string.Copy(textBox2.Text);
        Match MatchSpreadSheetId = RxSpreadSheetId.Match(URL);
        Match MatchSheetId = RxSheetId.Match(URL);
        String SpreadSheetId = MatchSpreadSheetId.Groups[1].Value;
        String SheetId = MatchSheetId.Groups[1].Value;
        String range = SheetId + "!A2:B";
            SpreadsheetsResource.ValuesResource.GetRequest request =
                    service.Spreadsheets.Values.Get(SpreadSheetId, range);

            // Prints the names and majors of students in a sample spreadsheet:
            // https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
            ValueRange response = request.Execute();
            IList<IList<Object>> values = response.Values;
            if (values != null && values.Count > 0)
            {
                Console.WriteLine("Name, Major");
                foreach (var row in values)
                {
                // Print columns A and E, which correspond to indices 0 and 4.
                Console.WriteLine("{0}, {1}", row[0], row[1]);
                richTextBox1.Text = (string)row[1];
                }
            }
            else
            {
                Console.WriteLine("No data found.");
            }
            Console.Read();
        
    }

Kevin Kevin · Accepted Answer · 2020-08-24T12:03:37

The google-sheets-api requires that the range name be a string, so the integer id of the sheet will not work. A work around would be to translate the id to a sheet name using the api. This could be accomplished in C# as below:

private static string GetSheetNameByID(string SpreadSheetID, int SheetID, SheetsService Service)
{
    //we just want the sheet name so don't retrieve any data
    List<string> ranges = new List<string>();
    bool includeGridData = false;

    SpreadsheetsResource.GetRequest request = Service.Spreadsheets.Get(SpreadSheetID);
    request.Ranges = ranges;
    request.IncludeGridData = includeGridData;

    // To execute asynchronously in an async method, replace `request.Execute()` as shown:
    Data.Spreadsheet response = request.Execute();

    foreach(var sheet in response.Sheets)
    {
        if (sheet.Properties.SheetId == SheetID)
        {
            return sheet.Properties.Title;
        }
    }

    return null;
}

from your code you could call:

var name = GetSheetNameByID(SpreadSheetId, SheetId, service);

Find the sheet with SpreadSheetId and SheetId C#

1 Answers