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

2022-01-10 00:00:00 google-sheets google-drive-api google-spreadsheet-api java

我迷路了.我正在尝试将 Java 与 Google 电子表格连接起来，虽然 API 的文档在检索数据方面是完整的(并且工作正常)，但我无法弄清楚如何写入电子表格.

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.

谁能提供一个完整示例(包括必要的导入和所有)，说明如何在 Google 电子表格中进行非常简单的数据输入(例如，在 A1 单元格中输入asdf"Sheet1)?

Could anyone, please provide a full example (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)?

如果这样的教程存在于某处，我找不到它 - 任何指针将不胜感激.

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

非常感谢，Zsolt

推荐答案

这里是修改版的快速入门教程代码，用来写.

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.jackson2.JacksonFactory;
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 JacksonFactory 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 = JacksonFactory.getDefaultInstance();

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

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

Per the quick start tutorial, you'll need to download the certification file from 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
    }
}

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

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

相关文章