Google Sheets is Google’s cloud-based spreadsheet service. It runs directly in the browser, requires no installation, and all documents are automatically saved to Google Drive. Essentially, it is an alternative to Microsoft Excel, but with a strong focus on online collaboration. In Stimulsoft, each worksheet in Google Sheets can be used as a separate data table, which can serve as the basis for building a report or dashboard. Data can be retrieved from both publicly accessible spreadsheets and those with restricted access. The only difference lies in the need to provide authorization parameters.

Google Sheets data adapter

The Google Sheets data source can only be used in the report generator for the .NET platform. In the report engines for JavaScript, PHP, Python, and Java, this data source is not supported. In Stimulsoft BI Designer, the adapter is installed automatically when creating a Google Sheets data source or when opening a report that contains it. When embedding Stimulsoft components into a project, it’s sufficient to add the NuGet package Stimulsoft.Data.GoogleSheets.

Retrieving data from public Google Sheets

Creating a Google Sheets data source is almost identical to creating any other data source:

• Open the Dictionary;
• Select New Data Source from the New menu;
• On the Google tab, choose Google Sheets;
• In the General Access parameter, select Anyone with the Link;
• In the Spreadsheet Id / URL field, paste the link to the Google Sheets document;
• Click OK.

After this, the Select Data window will display the available tables (worksheets) that can be used for designing reports and dashboards.

Retrieving data from restricted Google Sheets

The algorithm of creating a data source is similar, but in the connection window the General Access parameter must be set to Restricted. In this case, in addition to the Google Sheets link, you will also need to provide a Client ID and Client Secret.

Stimulsoft isn’t related to the Google Cloud Console project. However, at the time of writing, the process of creating a project looks as follows:

1. Go to Google Cloud Console;
2. In the top menu, select Select a project - New Project;
3. Enter a project name;
4. Click Create;
5. Navigate to APIs & Services - Library - Google Sheets API;
6. Select APIs & Services - Credentials;
7. Create Credentials by choosing Create Credentials - OAuth client ID;
8. Specify the application name;
9. Save the changes;
10. Choose the application type: Desktop app (for local applications) or Web application (if it will be used in a web app);
11. Click Create;
12. A window will appear showing your Client ID and Client Secret.

Next, copy the values of Client ID and Client Secret and paste them into the connection creation window. Then click OK in the new connection dialog. After that, in the Select Data window, choose the tables (worksheets) you want to use for designing reports and dashboards.

This way, you can easily retrieve data from Google Sheets and use it as the foundation for creating reports and dashboards.