如何使用 Sheets APIv4 Java 在 Google 表格中插入一行

Question

如果我想在指定索引处使用 Java 将一行插入到 Google 工作表中，然后写入该行，我将如何处理？

Answer 1

经过大量的谷歌搜索，一些挣扎和适应另一个答案后，我能够弄清楚这一点。

注意：您可以生成自己的credentials.json. 这位于大多数 IDE 中的“资源”文件夹中。

这是我使用的完整代码——您需要的相关方法是insertRow下面的方法，该方法是从调用的。writeTest 在正常情况下，我只会发布有问题的方法，但是工作表 API 中的“快速启动”肯定是缺乏的所以这里是完整的来源：

类和进口

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.javanet.NetHttpTransport;

import com.google.api.client.json.JsonFactory;

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.*;

import java.io.IOException;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.security.GeneralSecurityException;

import java.util.Arrays;

import java.util.Collections;

import java.util.List;

public class SheetsTest {

    private static final String APPLICATION_NAME = "Metrics project";

    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();

    private static final String TOKENS_DIRECTORY_PATH = "tokens";

    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);

    private static final String CREDENTIALS_FILE_PATH = "/credentials.json";

获取凭据：

private static Credential getCredentials(final NetHttpTransport HTTP_TRANSPORT) throws IOException{

    InputStream in = SheetsTest.class.getResourceAsStream(CREDENTIALS_FILE_PATH);

    GoogleClientSecrets clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));

    GoogleAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow.Builder(

            HTTP_TRANSPORT, JSON_FACTORY, clientSecrets, SCOPES)

            .setDataStoreFactory(new FileDataStoreFactory(new java.io.File(TOKENS_DIRECTORY_PATH)))

            .setAccessType("offline")

            .build();

    LocalServerReceiver receiver = new LocalServerReceiver.Builder().setPort(8888).build();

    return new AuthorizationCodeInstalledApp(flow, receiver).authorize("user");

}

写测试

//Creates a new row at row 2 then writes data to it...

static void writeTest(Report r, String sheetID, String range)throws GeneralSecurityException, IOException{

    final NetHttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();

    ValueRange requestBody = requestBuilder(r, range);

    Sheets sheetsService = new Sheets.Builder(HTTP_TRANSPORT,JSON_FACTORY, getCredentials(HTTP_TRANSPORT))

            .setApplicationName(APPLICATION_NAME)

            .build();

    insertRow(sheetsService, sheetID);

    Sheets.Spreadsheets.Values.Append request =

            sheetsService.spreadsheets().values().append(sheetID, range, requestBody);

    request.setValueInputOption("RAW");

    //request.setInsertDataOption("INSERT_ROWS");

    AppendValuesResponse resp = request.execute();

    System.out.println(resp);

}

插入行

//Inserts row at index, this is hardcoded to row 2 (0-indexed)

private static void insertRow(Sheets ss, String sheetID)throws IOException{

    InsertDimensionRequest insertRow = new InsertDimensionRequest();

    insertRow.setRange(new DimensionRange().setDimension("ROWS").setStartIndex(1).setEndIndex(2).setSheetId(0));

    BatchUpdateSpreadsheetRequest r = new BatchUpdateSpreadsheetRequest().setRequests(Arrays.asList(

            new Request().setInsertDimension(insertRow)

    ));

    ss.spreadsheets().batchUpdate(sheetID, r).execute();

}

请求生成器

//Populate ValueRange

static ValueRange requestBuilder( Report r, String range){

    ValueRange v = new ValueRange()

        .setValues(Arrays.asList(

            Arrays.asList(r.value1),

            Arrays.asList(r.value2),

            Arrays.asList(r.value3),

            Arrays.asList(r.value4),

            Arrays.asList(r.value5)

        ))

        .setMajorDimension("ROWS")

        .setRange(range)

    ;

    return v;

}

Answer 2

我不允许对 Elric 的回答发表评论，但我只想更正一下。

要使用您的值添加单行，请在方法中：

请求生成器\

它应该.setMajorDimension("COLUMNS")代替"ROWS.

我希望这是有帮助的。