使用 Java 将数据写入 Google 电子表格

Write data into Google Spreadsheet w/ Java

提问by Zsolt Balla

I'm lost on this one. I'm trying to connect Java with Google spreadsheet, and although the API's documentation is complete on retrieving data (and it is working fine), I am unable to figure out how to write into the spreadsheet.

我迷失在这一点上。我正在尝试将 Java 与 Google 电子表格连接起来，尽管 API 的文档在检索数据方面是完整的（并且工作正常），但我无法弄清楚如何写入电子表格。

Could anyone, please provide a fullexample (with the necessary imports and all) on how to do a very simple data entry into a Google Spreadsheet (say, enter "asdf" into the A1 cell of Sheet1)?

任何人都可以提供一个完整的示例（包括必要的导入和所有内容），说明如何将非常简单的数据输入到 Google 电子表格中（例如，在 Sheet1 的 A1 单元格中输入“asdf”）？

If a tutorial like this exists somewhere, I could not find it - any pointers would be much appreciated.

如果某个地方存在这样的教程，我找不到它 - 任何指针将不胜感激。

Thank you very much, Zsolt

非常感谢，Zsolt

回答by stuart_gunn

Here is a modified version of the quick start tutorial code, to perform a write.

这是快速入门教程代码的修改版本，用于执行写入。

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.extensions.java6.auth.oauth2.AuthorizationCodeInstalledApp;
import com.google.api.client.extensions.jetty.auth.oauth2.LocalServerReceiver;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.javanet.GoogleNetHttpTransport;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.json.Hymanson2.HymansonFactory;
import com.google.api.client.util.store.FileDataStoreFactory;
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 java.io.*;
import java.util.*;

public class SheetsIntegration {

    private static HttpTransport transport;
    private static HymansonFactory jsonFactory;
    private static FileDataStoreFactory dataStoreFactory;

I get a permissions warning at this line, but it's not fatal

我在这一行收到权限警告，但这并不致命

    private static final java.io.File DATA_STORE_DIR = new java.io.File(System.getProperty("user.home"), ".credentials/sheets.googleapis.com.json");

Quick start tutorial uses readonly scope instead

快速入门教程使用只读范围代替

    private static List<String> scopes = Arrays.asList(SheetsScopes.SPREADSHEETS);

    public SheetsIntegration() {
        try {
            transport = GoogleNetHttpTransport.newTrustedTransport();
            dataStoreFactory = new FileDataStoreFactory(DATA_STORE_DIR);
            jsonFactory = HymansonFactory.getDefaultInstance();

            service = getSheetsService();
        } catch (Exception e) {
            // handle exception
        }
    }

Per the quick start tutorial, you'll need to download the certification file from Google.

根据快速入门教程，您需要从 Google 下载认证文件。

public static Credential authorize() throws IOException {
    // Load client secrets.
    File cfile = new File("certs/cert.json");
    cfile.createNewFile();
    GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(jsonFactory, new InputStreamReader(new FileInputStream(cfile)));

    // Build flow and trigger user authorization request.
    GoogleAuthorizationCodeFlow flow =
            new GoogleAuthorizationCodeFlow.Builder(
                    transport, jsonFactory, clientSecrets, scopes)
                    .setDataStoreFactory(dataStoreFactory)
                    .setAccessType("offline")
                    .build();
    Credential credential = new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver()).authorize("user");
    return credential;
}

public static Sheets getSheetsService() throws IOException {
    Credential credential = authorize();
    return new Sheets.Builder(transport, jsonFactory, credential)
            .setApplicationName("INSERT_YOUR_APPLICATION_NAME")
            .build();
}

public void writeSomething(List<Data> myData) {

    try {
        String id = "INSERT_SHEET_ID";
        String writeRange = "INSERT_SHEET_NAME!A3:E";

        List<List<Object>> writeData = new ArrayList<>();
        for (Data someData: myData) {
            List<Object> dataRow = new ArrayList<>();
            dataRow.add(someData.data1);
            dataRow.add(someData.data2);
            dataRow.add(someData.data3);
            dataRow.add(someData.data4);
            dataRow.add(someData.data5);
            writeData.add(dataRow);
        }

        ValueRange vr = new ValueRange().setValues(writeData).setMajorDimension("ROWS");
        service.spreadsheets().values()
                .update(id, writeRange, vr)
                .setValueInputOption("RAW")
                .execute();
    } catch (Exception e) {
        // handle exception
    }
}

One other note - I had to add servlet-api.jar to my project.

另一个注意事项 - 我必须将 servlet-api.jar 添加到我的项目中。

回答by Zsolt Balla

OK, it took me a good few hours to figure it out finally, and the answer turns out to be easier than building an Ajax request from scratch. In the hope of saving hours and hours for others, here is the solution that worked for me.

好的，我花了好几个小时终于弄明白了，结果证明答案比从头开始构建 Ajax 请求要容易得多。希望为他人节省时间和时间，这是对我有用的解决方案。

Prereqs: I used the Quickstart tutorial of the Google Sheets API, to read from a table, that is pretty complex, but worked fine for me.

先决条件：我使用了Google Sheets API的快速入门教程来读取表格，这非常复杂，但对我来说效果很好。

After the tutorial I needed to amend a few things, though

教程结束后，我需要修改一些东西，不过

1, change the line

1、换线

private static final List<String> SCOPES =
        Arrays.asList(SheetsScopes.SPREADSHEETS_READONLY);

to

到

private static final List<String> SCOPES =
        Arrays.asList(SheetsScopes.SPREADSHEETS);

for obvious reasons (as we want to write the table, not only read it.

出于显而易见的原因（因为我们要写表，而不仅仅是读取它。

2, Delete the stored credentials that are stored in your user directory in a folder called /.credentials/

2，删除存储在您的用户目录中名为/.credentials/的文件夹中的存储凭据

One more note: there appears to be a method called

还有一个注意事项：似乎有一种方法叫做

spreadsheets.values.update()

but I couldn't get that working, as it requires a valueInputOptionparameter to be set, and hours of searching did not prove enough to find where can one set it.

但我无法让它工作，因为它需要valueInputOption设置一个参数，而且数小时的搜索并不足以找到可以在哪里设置它。

So, finally, I ended up with a method called

所以，最后，我得到了一个叫做

spreadsheets.values.batchUpdate()

Here's the code full method that did the trick of writing "Hello World!"into a table cell for me (as for imports, I used the same as in the Quickstart tutorial above):

这是代码完整方法，它"Hello World!"为我完成了写入表格单元格的技巧（至于导入，我使用了与上面的快速入门教程相同的方法）：

void WriteExample() throws IOException {
    Sheets service = getSheetsService();
    List<Request> requests = new ArrayList<>();

      List<CellData> values = new ArrayList<>();


      values.add(new CellData()
                .setUserEnteredValue(new ExtendedValue()
                        .setStringValue("Hello World!")));
        requests.add(new Request()
                .setUpdateCells(new UpdateCellsRequest()
                        .setStart(new GridCoordinate()
                                .setSheetId(0)
                                .setRowIndex(0)
                                .setColumnIndex(0))
                        .setRows(Arrays.asList(
                                new RowData().setValues(values)))
                        .setFields("userEnteredValue,userEnteredFormat.backgroundColor")));

        BatchUpdateSpreadsheetRequest batchUpdateRequest = new BatchUpdateSpreadsheetRequest()
                .setRequests(requests);
        service.spreadsheets().batchUpdate(spreadsheetId, batchUpdateRequest)
                .execute();
    }

The SheetId is the ID of the worksheet you are writing (it is always 0 for the first worksheet in a certain spreadheet, and you can get it from the URL for others: it's the part after #gid=

SheetId 是您正在编写的工作表的 ID（对于某个电子表格中的第一个工作表，它始终为 0，您可以从其他人的 URL 中获取它：它是后面的部分 #gid=

If you want to go into further complexities, like formatting or using formulas, you can - in this case, use the Java example provided here.

如果您想进一步研究复杂性，例如格式化或使用公式，您可以 - 在这种情况下，使用此处提供的Java 示例。

Hope it helps, Zsolt

希望它有帮助，Zsolt

回答by noogui

Make use of XmlHttpRequestrequest.

使用XmlHttpRequest请求。

I'll give you a quick demo where you will actually write on a google-spreadsheet. You will then apply the concepts here using your preferred language, Java.

我会给你一个快速演示，你将在其中实际写在谷歌电子表格上。然后，您将使用首选语言 Java 在此处应用这些概念。

1. open a google-spreadsheet to write on. Get it's spreadsheet ID
2. Go to oauth playgroundand navigate to Google Sheets API v4
3. Choose https://www.googleapis.com/auth/drivepermission. Click Authorize APIs. Allow the permission.
4. On Step 2, press Exchange authorization code for tokens button.

5. On Step 3, Paste this on Request URI:

   https://sheets.googleapis.com/v4/spreadsheets/{SpreadsheetID}/values/Sheet1!A1?valueInputOption=USER_ENTERED

1. 打开一个谷歌电子表格来写。获取它的电子表格 ID
2. 转到oauth playground并导航到Google Sheets API v4
3. 选择https://www.googleapis.com/auth/drive权限。单击授权 API。允许权限。
4. 在第 2 步，按 Exchange 令牌授权码按钮。

5. 在第 3 步，将其粘贴到请求 URI 上：

   https://sheets.googleapis.com/v4/spreadsheets/{SpreadsheetID}/values/Sheet1!A1?valueInputOption=USER_ENTERED

HTTP Method is PUT.

HTTP 方法是PUT。

Place this inside your Enter Request Body

把它放在你的输入请求正文中

{
  "range":"Sheet1!A1",
  "majorDimension": "ROWS",
  "values": [
    ["Hello World"]
  ],
}

Click Send the Request. If you get 200 OKresponse, expect "Hello World" to be written in A1 cell of your spreadsheet. You can read more about writing data in Sheets v4 here.

单击发送请求。如果您收到200 OK回复，请在电子表格的 A1 单元格中写入“Hello World”。您可以在此处阅读有关在 Sheets v4 中写入数据的更多信息。

How to do this in Java? Learn about AJAX or XHR implementation in Java. Check this tutorialto get you started.

如何在 Java 中做到这一点？了解 Java 中的 AJAX 或 XHR 实现。查看本教程以帮助您入门。