更新Google Sheets单元格时返回404 Not Found错误的原因排查

尝试更新Google Sheets表格中的单元格，已确认spreadsheetId、凭证、工作表“Names”及单元格“B2”均正确，但调用API时返回404 Not Found错误。

堆栈跟踪： #

com.google.api.client.googleapis.json.GoogleJsonResponseException: 404 Not Found
PUT https://sheets.googleapis.com/v4/spreadsheets/1OF8UZnI8AZ0ExmTxXdq7NmXCQo_8dg4pTV6Z8e4KQKQ/values/%D0%9B%D0%B8%D1%81%D1%821!B2?valueInputOption=RAW
{
  "code" : 404,
  "errors" : [ {
    "domain" : "global",
    "message" : "Requested entity was not found.",
    "reason" : "notFound"
  } ],
  "message" : "Requested entity was not found.",
  "status" : "NOT_FOUND"
}

代码： #

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

import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Collections;
import java.util.List;

public class GoogleSheets {

    private static final String APPLICATION_NAME = ".....";
    private static final JsonFactory JSON_FACTORY = JacksonFactory.getDefaultInstance();
    private static final List<String> SCOPES = Collections.singletonList(SheetsScopes.SPREADSHEETS);
    private static final String CREDENTIALS_FILE_PATH = ".......";
    private static final String TOKENS_DIRECTORY_PATH = "tokens";

    private Sheets sheetsService;

    public GoogleSheets() throws Exception {
        final com.google.api.client.http.HttpTransport HTTP_TRANSPORT = GoogleNetHttpTransport.newTrustedTransport();
        this.sheetsService = new Sheets.Builder(HTTP_TRANSPORT, JSON_FACTORY, getCredentials(HTTP_TRANSPORT))
                .setApplicationName(APPLICATION_NAME)
                .build();
    }

    private Credential getCredentials(final com.google.api.client.http.HttpTransport HTTP_TRANSPORT) throws Exception {
        InputStream in = new FileInputStream(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");
    }

    public void updateCellValue(String spreadsheetId, String range, String value) {
        try {
            ValueRange body = new ValueRange().setValues(
                    Collections.singletonList(Collections.singletonList(value))
            );
            sheetsService.spreadsheets().values()
                    .update(spreadsheetId, range, body)
                    .setValueInputOption("RAW")
                    .execute();
            System.out.println("Cell updated: " + range + " with value: " + value);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        try {
            GoogleSheets service = new GoogleSheets();
            service.updateCellValue("1OF8UZnI8AZ0ExmTxXdq7NmXCQo_8dg4pTV6Z8e4KQKQ", "Names!B2", "123");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

从堆栈跟踪的请求URL可以看出，实际请求的工作表是俄文默认名称Лист1（URL编码为%D0%9B%D0%B8%D1%81%D1%821），而非代码中指定的Names，这是导致404错误的核心原因。以下是具体可能的原因和对应解决方法：

• 运行了旧版本代码：你可能修改了代码中的range参数为Names!B2但未重新编译运行，实际执行的仍是之前使用Лист1!B2的旧代码。解决方法：清理项目编译缓存，重新编译并运行最新版本的代码。
• 授权账号不匹配：本地tokens目录存储的授权token对应的Google账号，并非目标表格所属账号，该账号下对应ID的表格中仅存在默认俄文工作表Лист1，没有Names工作表。解决方法：删除tokens目录下的所有文件，重新执行代码完成授权，确保使用目标表格所属的账号登录。
• 工作表名称存在隐藏问题：你误以为工作表名为Names，但实际存在大小写差异（如实际是names）、空格或特殊字符，甚至表格中根本没有该名称的工作表。解决方法：打开目标表格，右键点击工作表标签选择“重命名”确认准确名称；或者使用工作表ID替代名称指定range（格式为'工作表ID'!B2，工作表ID可通过Sheets API的spreadsheets.get接口获取）。

内容的提问来源于stack exchange，提问作者Warlam