Read/Write Google sheet via Server without using OAuth

google sheets api key
google sheets api example
google sheets api service account
google sheets api postman
google sheets api has not been used in project
google sheets api with api key
google sheets api java
google oauth

Tried going through the internet and google docs they provide OAuth way only. Is there a way to read/write to google sheets with API Key and not OAuth.


After some research, Credential object from google-oath-client module can help. Download the .p12 file from the google account. Code for reading a google sheet without OAUth prompt below. This can also be used to write or append sheets with some modification :

package com.mycomp;

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.nm.vernacular.services.SpreadSheetsService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.security.GeneralSecurityException;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

/**
 * Created by ankushgupta & modified for SO.
 */

public class GoogleSheetsReader {

    private static final JsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
    private static final String KEY_FILE_LOCATION = "<Name of p12 file>.p12";
    private static final String SERVICE_ACCOUNT_EMAIL = "<email of google service account>";
    private static final String APPLICATION_NAME = "Google Sheets API";

    private static final Logger LOGGER = LoggerFactory.getLogger(GoogleSheetsReader.class);

    /**
     * Global instance of the scopes required by this quickstart.
     * If modifying these scopes, delete your previously saved credentials/ folder.
     */
    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);

    /**
     * Creates an authorized Credential object.
     * @return An authorized Credential object.
     * @throws IOException If there is no client_secret.
     */
    private Credential getCredentials() throws URISyntaxException, IOException, GeneralSecurityException {
        //Reading Key File
        URL fileURL = GoogleSheetsReader.class.getClassLoader().getResource(KEY_FILE_LOCATION);
        // Initializes an authorized analytics service object.
        if(fileURL==null) {
            fileURL = (new File("/resources/"+ KEY_FILE_LOCATION)).toURI().toURL();
        }
        // Construct a GoogleCredential object with the service account email
        // and p12 file downloaded from the developer console.
        HttpTransport httpTransport = GoogleNetHttpTransport.newTrustedTransport();
        return new GoogleCredential.Builder()
                .setTransport(httpTransport)
                .setJsonFactory(JSON_FACTORY)
                .setServiceAccountId(SERVICE_ACCOUNT_EMAIL)
                .setServiceAccountPrivateKeyFromP12File(new File(fileURL.toURI()))
                .setServiceAccountScopes(SCOPES)
                .build();
    }

    @Override
    public List<Object[]> readSheet(String nameAndRange, String key, int[] returnRange) throws GeneralSecurityException, IOException {
        final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        final String spreadsheetId = key;
        final String range = nameAndRange;
        try {
            Sheets service = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials())
                    .setApplicationName(APPLICATION_NAME)
                    .build();

            ValueRange response = service.spreadsheets().values()
                    .get(spreadsheetId, range)
                    .execute();
            List<List<Object>> values = response.getValues();

            int a = returnRange.length;
            List<Object[]> result = new LinkedList<>();

            if (values == null || values.isEmpty()) {
                return Collections.emptyList();
            } else {
                for (List row : values) {
                    if(row.size() >= a) {
                        Object[] objArr = new Object[a];
                        for(int i=0;i<a;i++) {
                            objArr[i] = row.get(returnRange[i]);
                        }
                        result.add(objArr);
                    }
                }
            }
            return result;
        } catch(Exception ex) {
            LOGGER.error("Exception while reading google sheet", ex);
        } finally {

        }
        return null;
    }

    public static void main(String[] args) {
     GoogleSheetsReader reader = new GoogleSheetsReader();
     reader.readSheet("<Sheet Name>!A2:B", "<sheets key from URL>", new int[]{0, 1});
    }
}

Authorize Requests | Sheets API, About authorization protocols; Authorizing requests with OAuth 2.0 Allows read​/write access to the user's sheets and their properties. API keys: A request that does not provide an OAuth 2.0 token must send an API key. Your application must use OAuth 2.0 to authorize requests. No other authorization protocols are supported. If your application uses Google Sign-In, some aspects of authorization are handled for you. Authorizing requests with OAuth 2.0. Requests to the Google Sheets API for non-public user data must be authorized by an authenticated user.


Python Quickstart | Sheets API, Prerequisites; Step 1: Turn on the Google Sheets API; Step 2: Install the Google google-api-python-client google-auth-httplib2 google-auth-oauthlib If there are no (valid) credentials available, let the user log in. in a web application, see Using OAuth 2.0 for Web Server Applications. Further reading. Method-2 : Read Google SpreadSheet using Flat File Source (as CSV) Another approach is export SpreadSheet as CSV format. Then use Flat File Source to read that data and load into Target (E.g. SQL Server) Method-1 : Read Google SpreadSheet using SSIS JSON / REST API Source. Now lets look at real steps to read Google SpreadSheet.


Using API key, you can read from google sheets, but only if the sheet is shared with public.

However to write to google sheets, you must you OAuth. See this link.

Google Spreadsheets and Python, to read and update a Google spreadsheet in Python using the gspread for e.g. internal use, Google Docs as a backend (consumed via JSON) is to read, write, and delete data from a Google Spreadsheet with just a Create credentials for a Web Server to access Application Data. Not ready yet? Google Sheets are often an excellent middle-ground, providing an easy-to-use collaborative platform with a familiar Excel-like interface. Accessing Google sheet data using OAuth and the Google Python API is a straightforward process, thanks to the (per usual) excellent Google documentation .


A beginners guide to Google OAuth and Google APIs, Jan 8, 2017 · 6 min read I will use Google Spreadsheet as the application for demonstrating the the requests made to the OAuth and Google servers and responses OAuth provides your App access to your personal data without Also, whenever I write just “OAuth”, I'm actually talking about OAuth  Note: Use of Google's implementation of OAuth 2.0 is governed by the OAuth 2.0 Policies. Google APIs use the OAuth 2.0 protocol for authentication and authorization. Google supports common OAuth 2.0 scenarios such as those for web server, client-side, installed, and limited-input device applications.


3 Approaches for Using the Google Sheets API in Node.js: A Tutorial, Google Sheets HTTP Requests Via V3; Google Sheets API v4 with to be published; Using the v3 method only allows reading and not writing Create an OAuth2 client with the given credentials, and then execute the * given  To write to a sheet, you will need the spreadsheet ID, the range(s) in A1 notation, and the data you wish to write arranged in an appropriate request body object. For more information about spreadsheet IDs and A1 notation, see Key Concepts in the Google Sheets API.


Using OAuth for a simple command line script to access Google's data, I needed to write a simple script to pull some data from a Google website. These support server-to-server access with authentication done via public/private key pair. Google's docs show such an HTTP request like this. If a bad guy were to get this token he could not read my calendar data, nor modify  You must use OAuth 2.0 to authorize requests. OAuth 1.0 is no longer supported and was disabled on May 5, 2015. If your application uses OAuth 1.0, you must migrate to OAuth 2.0 to use this API. Authorize requests with OAuth 2.0. Requests to the Google Sheets API for non-public user data must be authorized by an authenticated user.