将数据写入Google Spreadsheet w / Java [英] Write data into Google Spreadsheet w/ Java

问题描述

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

任何人都可以提供一个完整的示例（包含必要的导入和全部内容）关于如何向Google Spreadsheet输入非常简单的数据（例如输入asdf到Sheet1的A1单元格）？

如果这样的教程存在，我找不到它 - 任何指针都会非常感谢。

非常感谢您，
Zsolt

解决方案

这是一个修改版本

  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; 
导入com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets; 
导入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; 
导入com.google.api.services.sheets.v4.Sheets; 
导入com.google.api.services.sheets.v4.SheetsScopes; 
 import com.google.api.services.sheets.v4.model.ValueRange; 
 import java.io. *; 
 import java.util。*; 
 
公共类SheetsIntegration {
 
私有静态HttpTransport传输; 
私有静态JacksonFactory jsonFactory; 
 private static FileDataStoreFactory dataStoreFactory; 
  

我在这一行得到了一个权限警告，但它不是致命的

  private static final java.io.File DATA_STORE_DIR = new java.io.File（System.getProperty（user.home），.credentials / sheets。 googleapis.com.json）; 
  

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

  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）{
 //处理异常
} 
} 
  

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

  public static Credential authorize（）抛出IOException {
 //加载客户机密。 
文件cfile =新文件（certs / cert.json）; 
 cfile.createNewFile（）; 
 GoogleClientSecrets clientSecrets = GoogleClientSecrets.load（jsonFactory，new InputStreamReader（new FileInputStream（cfile）））; 
 
 //建立流程并触发用户授权请求。 
 GoogleAuthorizationCodeFlow flow = 
 GoogleAuthorizationCodeFlow.Builder（
 transport，jsonFactory，clientSecrets，scopes）
 .setDataStoreFactory（dataStoreFactory）
 .setAccessType（offline）
 .build（）; 
证书凭证=新的AuthorizationCodeInstalledApp（流，新的LocalServerReceiver（））。authorize（user）; 
返回凭证; 
 
 
 public static Sheets getSheetsService（）throws IOException {
 Credential credential = authorize（）; 
返回新的Sheets.Builder（transport，jsonFactory，凭证）
 .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< 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）{
 //处理异常
} 
} 
  

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

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.

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.

Thank you very much, 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
        }
    }

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
    }
}

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

这篇关于将数据写入Google Spreadsheet w / Java的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！