Is there a way to add a row on a Google Spreadsheet without OAuth using java?

Question

I've searched a lot of websites about how to do that using only the spreadsheet key, but with no success. I can retrieve it, get all the data I want, but I can't append a new row to it.

I've already published my spreadsheet to the web and made it public but I keep getting " com.google.gdata.util.ServiceException: Method Not Allowed".

That's how I'm trying to do it:

SpreadsheetService service =
            new SpreadsheetService("MySpreadsheetIntegration-v1");

        try {

        URL url = FeedURLFactory.getDefault().getWorksheetFeedUrl(SpreadsheetRequest.KEY, "public", "full"); //KEY = "1cenwTRlrGMzMTufkK27IIgAHvEGwVKR9Yr59Qako3u0";

        WorksheetFeed worksheetFeed = service.getFeed(url, WorksheetFeed.class);
        List<WorksheetEntry> worksheets = worksheetFeed.getEntries();
        WorksheetEntry worksheet = worksheets.get(0);

        URL listFeedUrl = worksheet.getListFeedUrl();

        ListFeed listFeed = service.getFeed(listFeedUrl, ListFeed.class);
        List<ListEntry> list = listFeed.getEntries();

       //Checking all the entries, works like a charm
        for (ListEntry row : list) {
            Log.i("INSERT",row.getTitle().getPlainText() + "\t"
                    + row.getPlainTextContent());
        }

        ListEntry row = new ListEntry();

        row.getCustomElements().setValueLocal("id", "21");
        row.getCustomElements().setValueLocal("type", si.getType().toString());
        row.getCustomElements().setValueLocal("longitude", String.valueOf(si.getLongitude()));
        row.getCustomElements().setValueLocal("latitude", String.valueOf(si.getLatitude()));
        row.getCustomElements().setValueLocal("last_maint", String.valueOf(si.getLast()));
        row.getCustomElements().setValueLocal("inst_date", String.valueOf(si.getInst()));

        row = service.insert(listFeedUrl, row); //Exception is thrown here

        return true;
    }

Eranda · Answer 1 · 2015-08-30T14:02:24.163

1

Answer is No. OAuth token is like a key which allows Google to identify who is reading or who is writing to the spreadsheet and it won't allow if you don't have the correct access level. Even when you open Google spreadsheet with your browser the browser will generate an OAuth token from the Google identity provider and use that token to check whether your access level.

Update Here if you see how my browser handle the request done to the google spread sheet it will first redirect to the identity provider. Most of the time you need to simulate that in order to work.

edited Aug 30 '15 at 14:02

answered Aug 30 '15 at 01:56

Eranda

1,439
1
17
30

The thing I find weird is that I can access and retrieve the data in a spreadsheet without OAuth (using only the spreadsheet key), the code above proves it! According to @MirrorTowers here , it is possible do to what I want, but he doesn't explain how (he says it's possible in the comments). Is he wrong or am i missing something? – Bruno Lopes Aug 30 '15 at 02:29
It is public to anyone on the internet to edit: https://docs.google.com/spreadsheets/d/1cenwTRlrGMzMTufkK27IIgAHvEGwVKR9Yr59Qako3u0/edit – Bruno Lopes Aug 30 '15 at 02:39

score 0 · Answer 2 · edited May 23 '17 at 10:30

0

There is no way you can access or manipulate Google Drive API without OAUTH2.0. Additionally, Google Drive API wouldn't even work on private networks.

Fortunately, OAUTH2.0 is mandatory!!

This is the OAUTH2.0 Java code for spreadsheet----->

edited May 23 '17 at 10:30

Community

1
1

answered Aug 30 '15 at 02:03

score 0 · Answer 3 · answered Mar 21 '23 at 10:36

Using a service account it is possible to write to a google sheet without the need of OAuth.

Steps:

Create a service account
Delegating domain-wide authority to the service account
Give google sheets permission to the service account.
Download the Service account json file.
Use the service account json file from Java code to write to the desired Google Sheets.
Needless to say the google sheet has to be owned by you or at least you should have write access to the google sheet.

Here is kotlin code that can be used to write to Google Sheet with the service account json.

fun authorize(): Credential {

    // build GoogleClientSecrets from JSON file

    return GoogleCredential.fromStream(ClassPathResource("service-account.json").inputStream)
        .createScoped(arrayListOf(SheetsScopes.SPREADSHEETS))
        .createDelegated("your@email.com")
}

fun getSheetsService(): Sheets? {
    val credential: Credential = authorize()
    return Sheets.Builder(
        GoogleNetHttpTransport.newTrustedTransport(),
        JacksonFactory.getDefaultInstance(), credential
    )
        .setApplicationName("Your Application Name")
        .build()
}

fun writeToSheet() {
    val sheetsService = getSheetsService()
    val arrayListOf: List<ArrayList<Any>> = arrayListOf(
        arrayListOf("Row1 Column1", "Row1 Column2"),
        arrayListOf("Row2 Column1", "Row2 Column2")
    )
    val appendBody: ValueRange = ValueRange().setValues(arrayListOf)
    val appendResult: AppendValuesResponse = sheetsService.spreadsheets().values()
        .append("SpreadsheetId", "A1", appendBody)
        .setValueInputOption("USER_ENTERED")
        .setInsertDataOption("INSERT_ROWS")
        .setIncludeValuesInResponse(true)
        .execute()
}

Is there a way to add a row on a Google Spreadsheet without OAuth using java?

3 Answers3

Linked