title: Appending data to a Google Sheet from C# called SqlCommand
body: <p>I started with the basics and learned how to insert a fixed bit of rows to a Google Sheet from my program now I've been trying to figure out how to append output from a SQL. I have a C# program that calls a stored procedure or it can do a <code>select * from table</code>. Simply trying to insert the results/output of that select into a Google Sheet. Every example I find is dealing with hard coded fixed rows and columns. My select produces less than 2000 lines of output. Appreciate the help!</p>
  
  <pre><code>namespace CS_Gsheet1
  {
      class Program
      {
          // If modifying these scopes, delete your previously saved credentials
          // at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
          //static string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly };
          //static string ApplicationName = "Google Sheets API .NET Quickstart";
          static string[] Scopes = { SheetsService.Scope.Spreadsheets };
          static string ApplicationName = "Test3"; 
  
          static void Main(string[] args)
          {
              var service = AuthorizeGoogleApp();
  
              String spreadsheetId = "sheetIDstring"; 
  
              // Write to specified sheet
              String writeRange = "Sheet1!A1:K";
              ValueRange valueRange = new ValueRange { MajorDimension = "ROWS" };
  
              IList&lt;object&gt; dataList = new List&lt;object&gt;();
  
              using(SqlConnection myConnection = new SqlConnection("connectionstring"))
              {
                  myConnection.Open();
  
                  using(SqlCommand cmd = new SqlCommand("storedproc-selectsmultiplecolumnsandrows", myConnection))
                  {
                      cmd.CommandType = CommandType.StoredProcedure;
  
                      using(SqlDataReader reader = cmd.ExecuteReader())
                      {
                          while (reader.Read())
                          {
                              // dataList.Add(reader.GetString(0));
                              // dataList.Add(reader.GetValue(0));
                              IList&lt;IList&lt;Object&gt;&gt; values = new List&lt;IList&lt;Object&gt;&gt;();
                              values.Add(dataList);
                          }
                      }
                  }
              }
  
              // Data is accessible through the DataReader object here.
              ValueRange valueDataRange = new ValueRange() { MajorDimension = "ROWS" };
  
              valueDataRange.Values = new List&lt;IList&lt;object&gt;&gt; { dataList };
  
              int c = dataList.Count;
              Console.WriteLine("dataList Count {0}", c);
  
              for (int i = 0; i &lt; c; i++)
              {
                  // API to append data to sheet
                  SpreadsheetsResource.ValuesResource.AppendRequest appendRequest = service.Spreadsheets.Values.Append(valueDataRange, spreadsheetId, writeRange);
                  appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
                  appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
                  AppendValuesResponse appendValueResponse = appendRequest.Execute();
              }
          }
  
          private static SheetsService AuthorizeGoogleApp()
          {
              UserCredential credential;
  
              using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.ReadWrite))
              {
                  string credPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                  credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-quickstart.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,
                  });
  
                  return service;
              }
          }
      }
  }
  </code></pre>