如何将Google Sheets多行列数据转为单行序列？能否用Python等实现？

可以用Python、Java或C语言实现这个需求，核心逻辑是读取指定区域的多行4列数据，按「行内从左到右、逐行向下」的顺序拼接成单行，再写回Google Sheets。以下是各语言的具体实现示例：

Python 实现 #

依赖gspread和oauth2client库操作Google Sheets API，步骤清晰易上手：

1. 先安装依赖：

pip install gspread oauth2client

2. 代码示例：

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# 授权配置（需先在Google Cloud创建服务账号并下载密钥文件）
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("your-service-account-key.json", scope)
client = gspread.authorize(creds)

# 打开目标工作表
sheet = client.open("你的表格名称").sheet1

# 读取指定行数的4列数据（这里示例读取前10行，可改为用户输入或自动获取选中范围）
selected_range = f"A1:D10"
selected_rows = sheet.get(selected_range)

# 合并数据：跳过空行，按行优先拼接
merged_data = []
for row in selected_rows:
    if all(cell.strip() == "" for cell in row):
        continue
    merged_data.extend(row[:4])  # 确保只取每行前4列

# 将合并结果写入E1开始的单行
sheet.update("E1", [merged_data])

Java 实现 #

使用Google Sheets官方Java客户端，适合Java技术栈开发者：

1. Maven依赖配置：

<dependency>
    <groupId>com.google.api-client</groupId>
    <artifactId>google-api-client</artifactId>
    <version>1.34.1</version>
</dependency>
<dependency>
    <groupId>com.google.oauth-client</groupId>
    <artifactId>google-oauth-client-jetty</artifactId>
    <version>1.34.1</version>
</dependency>
<dependency>
    <groupId>com.google.apis</groupId>
    <artifactId>google-api-services-sheets</artifactId>
    <version>v4-rev20220715-2.0.0</version>
</dependency>

2. 代码示例：

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.json.gson.GsonFactory;
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.InputStreamReader;
import java.security.GeneralSecurityException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class SheetsDataMerger {
    private static final String APP_NAME = "Sheets Merge Tool";
    private static final GsonFactory JSON_FACTORY = GsonFactory.getDefaultInstance();
    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);
    private static final String CRED_PATH = "/credentials.json";

    private static Credential getCredentials() throws Exception {
        var in = SheetsDataMerger.class.getResourceAsStream(CRED_PATH);
        var secrets = GoogleClientSecrets.load(JSON_FACTORY, new InputStreamReader(in));

        var flow = new GoogleAuthorizationCodeFlow.Builder(
                GoogleNetHttpTransport.newTrustedTransport(), JSON_FACTORY, secrets, SCOPES)
                .setDataStoreFactory(new FileDataStoreFactory(new java.io.File("tokens")))
                .setAccessType("offline")
                .build();
        return new AuthorizationCodeInstalledApp(flow, new LocalServerReceiver.Builder().setPort(8888).build())
                .authorize("user");
    }

    public static void main(String[] args) throws Exception {
        var service = new Sheets.Builder(GoogleNetHttpTransport.newTrustedTransport(), JSON_FACTORY, getCredentials())
                .setApplicationName(APP_NAME)
                .build();

        String sheetId = "你的表格ID";
        String readRange = "Sheet1!A1:D"; // 读取所有行的前4列
        var response = service.spreadsheets().values().get(sheetId, readRange).execute();
        var values = response.getValues();

        List<Object> mergedData = new ArrayList<>();
        for (List<Object> row : values) {
            if (row.size() < 4) continue;
            mergedData.addAll(row.subList(0, 4));
        }

        // 写入合并后的数据到E1开始的单行
        var body = new ValueRange().setValues(Collections.singletonList(mergedData));
        service.spreadsheets().values().update(sheetId, "Sheet1!E1", body)
                .setValueInputOption("RAW")
                .execute();
    }
}

C 实现 #

借助libcurl和cJSON库调用Google Sheets API，适合C语言开发者：

1. 编译依赖：安装libcurl和cJSON库，编译命令示例：

gcc -o sheets_merge sheets_merge.c -lcurl -lcjson

2. 核心代码示例：

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <curl/curl.h>
#include <cjson/cJSON.h>

#define SHEET_ID "你的表格ID"
#define ACCESS_TOKEN "你的OAuth2访问令牌"

size_t write_cb(void *contents, size_t size, size_t nmemb, void *userp) {
    strncpy(userp, contents, size * nmemb);
    ((char*)userp)[size * nmemb] = '\0';
    return size * nmemb;
}

int main() {
    CURL *curl;
    char read_buf[10240] = {0};
    char url[256];

    // 读取数据
    snprintf(url, sizeof(url), "https://sheets.googleapis.com/v4/spreadsheets/%s/values/Sheet1!A1:D", SHEET_ID);
    curl = curl_easy_init();
    if (curl) {
        struct curl_slist *headers = NULL;
        char auth_hdr[128];
        snprintf(auth_hdr, sizeof(auth_hdr), "Authorization: Bearer %s", ACCESS_TOKEN);
        headers = curl_slist_append(headers, auth_hdr);
        headers = curl_slist_append(headers, "Content-Type: application/json");

        curl_easy_setopt(curl, CURLOPT_URL, url);
        curl_easy_setopt(curl, CURLOPT_HTTPHEADER, headers);
        curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, write_cb);
        curl_easy_setopt(curl, CURLOPT_WRITEDATA, read_buf);

        curl_easy_perform(curl);
        curl_easy_cleanup(curl);
        curl_slist_free_all(headers);
    }

    // 解析并合并数据
    cJSON *root = cJSON_Parse(read_buf);
    cJSON *values = cJSON_GetObjectItem(root, "values");
    cJSON *merged_arr = cJSON_CreateArray();

    for (int i = 0; i < cJSON_GetArraySize(values); i++) {
        cJSON *row = cJSON_GetArrayItem(values, i);
        for (int j = 0; j < 4 && j < cJSON_GetArraySize(row); j++) {
            cJSON_AddItemToArray(merged_arr, cJSON_Duplicate(cJSON_GetArrayItem(row, j), 1));
        }
    }

    // 准备写入请求体
    cJSON *body = cJSON_CreateObject();
    cJSON_AddItemToObject(body, "values", cJSON_CreateArray());
    cJSON_AddItemToArray(cJSON_GetObjectItem(body, "values"), merged_arr);
    char *post_data = cJSON_Print(body);

    // 写入数据
    snprintf(url, sizeof(url), "https://sheets.googleapis.com/v4/spreadsheets/%s/values/Sheet1!E1?valueInputOption=RAW", SHEET_ID);
    curl = curl_easy_init();
    if (curl) {
        struct curl_slist *headers = NULL;
        char auth_hdr[128];
        snprintf(auth_hdr, sizeof(auth_hdr), "Authorization: Bearer %s", ACCESS_TOKEN);
        headers = curl_slist_append(headers, auth_hdr);
        headers = curl_slist_append(headers, "Content-Type: application/json");

        curl_easy_setopt(curl, CURLOPT_URL, url);
        curl_easy_setopt(curl, CURLOPT_HTTPHEADER, headers);
        curl_easy_setopt(curl, CURLOPT_CUSTOMREQUEST, "PUT");
        curl_easy_setopt(curl, CURLOPT_POSTFIELDS, post_data);

        curl_easy_perform(curl);
        curl_easy_cleanup(curl);
        curl_slist_free_all(headers);
    }

    cJSON_Delete(root);
    cJSON_Delete(body);
    free(post_data);
    return 0;
}

通用注意事项 #

• 无论用哪种语言，都需要先在Google Cloud控制台开启Google Sheets API，并配置好权限（服务账号或OAuth2授权）
• 选中区域自动识别：若要实现选中后自动处理，可通过API获取当前工作表的选中范围；也可以简化为让用户输入起始/结束行号来指定处理范围
• 空行处理：示例代码中加入了跳过空行的逻辑，可根据实际需求调整

内容的提问来源于stack exchange，提问作者Cav O'leary