locked
Google Sheets with ASP.NET c# RRS feed

  • Question

  • User-418973555 posted

    trying to basically read data from google sheet from asp.net application

    not really know it would work,,

    error here : credential = GoogleCredential.FromFile(@"htt.....

    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Threading;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Google.Apis;
    using Google.Apis.Auth.OAuth2;
    using Google.Apis.Services;
    using Google.Apis.Sheets.v4;
    using Google.Apis.Util.Store;
    
    static readonly string[] Scopes = { SheetsService.Scope.Spreadsheets };
        static readonly string ApplicationName = "tried out";
        static readonly string sheet = "sheetname";
        static readonly string SpreadsheetId = "1Asasdsdasdasdadsdasda";
        static SheetsService service;
        GoogleCredential credential;
    
        protected void Page_Load(object sender, EventArgs e)
        {
            credential = GoogleCredential.FromFile(@"http:\\www.abc.com\\Bin\\My Project-bsadas2313asd.json");  // tried bin/My Project-bsadas2313asd.json 
            
          
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });
          
    
    
    
            var range = $"{sheet}!A:E";
            SpreadsheetsResource.ValuesResource.GetRequest request =
                    service.Spreadsheets.Values.Get(SpreadsheetId, range);
            // Ecexuting Read Operation...
            var response = request.Execute();
            // Getting all records from Column A to E...
            IList<IList<object>> values = response.Values;
            if (values != null && values.Count > 0)
            {
                gv01.DataSource = values;
                gv01.DataBind();
            }
            else
            {
    
            }
    }

    Wednesday, January 8, 2020 9:31 AM

All replies

  • User281315223 posted

    You may want to ensure that your your path is correctly, specifically the local one as I'm not sure the ReadFile() method will be able to access a file that is publicly hosted. Ensure that your file is present in the bin directory and try this:

    var credentialPath = Application.StartupPath + "My Project-bsadas2313asd.json";
    credential = GoogleCredential.FromFile(credentialPath)
    

    Your Stack Trace should show you the path that is being attempted when your error occurs, so you might consider tweaking it to ensure that your expected file is at the appropriate location.

    Wednesday, January 8, 2020 4:07 PM
  • User-474980206 posted

    you have not given a valid file path. if the file is at the root of your site try:

      credential = GoogleCredential.FromFile(Server.MapPath(@"~/My Project-bsadas2313asd.json");             

    note: not sure if the space in the file name is supported.

    Wednesday, January 8, 2020 4:45 PM
  • User-418973555 posted

    okay, after activating google sheets api, shared googles sheets with generated email from service account, 

      var credential = GoogleCredential.FromStream(new FileStream(Server.MapPath(@"Bin/sassdsddsdsda.json"), FileMode.Open)).CreateScoped(Scopes);
            var service = new SheetsService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = ApplicationName,
            });
    
            // Define request parameters.  
            String spreadsheetId = "sasdasdasdsadsdasad";
            String range = $"{sheet}!A:E";
            SpreadsheetsResource.ValuesResource.GetRequest request =
                                    service.Spreadsheets.Values.Get(spreadsheetId, range);
            // Prints the names and majors of students in a sample spreadsheet:  
            ValueRange response = request.Execute();
            IList<IList<Object>> values = response.Values;
            if (values != null && values.Count > 0)
            {
                gv01.DataSource = values;
                gv01.DataBind();
            }
            else
            {
    
            }
    

    now not sure how to get the google sheets datafield and populate into gridview : 

    the google sheets datafield are :

    A B C
    1 firstproduct firstdesc
    2 product 1 Description 1 
    3 product 2 Description 2
    4 product 3 Description 3 
    5 product 4 Description 4 
    6 product 5 Description 5 
    7
       <div style="text-align:center"><asp:GridView ID="gv01" runat="server" AutoGenerateColumns="false">
                                         <Columns>
                                          
                                            <asp:BoundField DataField="firstproduct" HeaderText="firstproduct" />
                                            <asp:BoundField DataField="firstdesc" HeaderText="firstdesc" />
                                       
                                        </Columns>
                                    </asp:GridView>
                                    </div>

    A field or property with the name 'firstproduct' was not found on the selected data source.

    Thursday, January 9, 2020 3:25 AM
  • User281315223 posted

    You may want to consider placing a breakpoint after you read in your values to get an idea of what they look like and use that to dictate how to configure your GridView:

    IList<IList<Object>> values = response.Values;
    // Place a breakpoint here to look over the data

    Otherwise, if you are just considered with displaying it. You could consider setting the AutoGenerateColumns property to true on your GridView:

    <asp:GridView ID="gv01" runat="server" AutoGenerateColumns="true"></asp:GridView>

    Thursday, January 9, 2020 4:09 AM
  • User-418973555 posted

    tried both 

    AutoGeneratecolumns = "true" or "false"

    before that

    also cannot

    The data source for GridView with id 'gv01' did not have any properties or attributes from which to generate columns.  Ensure that your data source has content.

    above error after placing to true, but i think it didnt successfully fetched data b4 that

    Thursday, January 9, 2020 4:17 AM
  • User281315223 posted

    Did you try setting a breakpoint to see what the data within your response from the FromFile/FromStream was similar to the first part of my previous response? Without knowing exactly what the data looks like, it might be hard to determine how to configure your GridView.

    Based on what I'm seeing in the documentation, it looks like you could potentially construct a class to more easily bind these values to your grid:

    public class Product
    {
        public string Name { get; set; }
        public string Description { get; set; }
    }

    And then create a collection of these using your values:

    IList<IList<Object>> values = response.Values;
    if (values != null && values.Count > 0)
    {
         // Build your collection of Products
         var products = values.Select(x => new Product(){ Name = x[1], Description = x[2] });
         
         // Bind your products to the grid
         gv01.DataSource = products ;
         gv01.DataBind();
    }
    else
    {
           
    }

    Then just update your GridView to bind to those properties:

    <asp:GridView ID="gv01" runat="server" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="Name" HeaderText="Name" />
            <asp:BoundField DataField="Description" HeaderText="Description" />
        </Columns>
    </asp:GridView>

    Thursday, January 9, 2020 5:22 AM
  • User-418973555 posted

     CS0266: Cannot implicitly convert type 'object' to 'string'. An explicit conversion exists (are you missing a cast?)

     var products = values.Select(x => new Product(){ Name = x[1], Description = x[2] });
    Thursday, January 9, 2020 5:50 AM
  • User1535942433 posted

    Hi larnvok09,

    Accroding to your description,I suggest you could set breakpoints to know the structure of the response data.

    Then you need to check if the data source is obtained.

    You could accroding to the demo to write your codes.

    Best regards,

    Yijing Sun

    Thursday, January 9, 2020 9:52 AM
  • User-474980206 posted

    The data from the spreadsheet is list of rows, where each row is a list of columns. There are no data fields. You could use a linq query to convert the array of arrays to an array of objects. 

    Friday, January 10, 2020 1:11 AM
  • User-418973555 posted

    not sure whether it could run at offline mode

    while integrating with google sheets online

    Friday, January 10, 2020 1:37 AM
  • User1535942433 posted

    Hi larnvok09,

    not sure whether it could run at offline mode

    Could you tell us what you want to run at offline  mode?

    Do you want to get the google sheets datafield and populate into gridview at offline  mode?You must online and it will be success.

    Best regards,

    Yijing Sun

    Monday, January 13, 2020 9:50 AM