通过JavaScript API写入Google Sheet失败，集成Google Identity Services仍无效

我能通过Google Sheet API正常读取表格数据，但无法写入。已经尝试集成Google Identity Services完成登录，写入功能还是不能正常工作，且已确认在console.cloud.google.com完成了相关配置，求技术帮助。

相关代码 #

<!DOCTYPE html>
<html>
<head>
</head>
<body>

<button id="authorize_button" onclick="handleAuthClick()">Authorize</button>
<button id="signout_button" onclick="handleSignoutClick()">Sign Out</button>
<pre id="content" style="white-space: pre-wrap;"></pre>
<form id="violationForm">
    <h3>Detention Report</h3>
    <label for="duration">Select Duration:</label>
    <select id="duration" name="duration">
        <option value="15">15 minutes</option>
        <option value="30">30 minutes</option>
    </select>
    <br><br>

    <label>Reasons for Report:</label>
    <ul>
        <li>
            <input type="checkbox" id="tardy" name="reason" value="tardy">
            <label for="followDirections">Tardy</label>
        </li>
        <li>
            <input type="checkbox" id="other" name="reason" value="Other">
            <label for="other">Other:</label>
            <input type="text" id="otherDescription" name="otherDescription" placeholder="Describe other reason">
        </li>
    </ul>
    <br>
    <input type="button" id="submitDetention" name="submitDetention" value="Submit Detention">
</form>

<script>
const CLIENT_ID = 'the client ID';
const SCOPES = 'https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/userinfo.profile https://www.googleapis.com/auth/userinfo.email';
const discoveryUrl = 'https://sheets.googleapis.com/$discovery/rest?version=v4';
const redirectUri = 'https://thewebsiteImusing.com/callback';
const API_KEY = 'theAPIKey';
const SHEET_ID = 'theSheetID';
let tokenClient;
let gapiInited = false;
let gisInited = false;
var detentionStudents = []

////////////Authentication and Google API Specific Code///////////////
function gapiLoaded() {
    gapi.load('client', initializeGapiClient);
}
async function initializeGapiClient() {
    await gapi.client.init({
        apiKey: API_KEY,
    client_id: CLIENT_ID,
        discoveryDocs: [discoveryUrl],
        scope: SCOPES,
        redirect_uri: redirectUri,
    });
    gapiInited = true;
}

function gisLoaded() {
    tokenClient = google.accounts.oauth2.initTokenClient({
        client_id: CLIENT_ID,
        scope: SCOPES,
        callback: '', // defined later
    });
    gisInited = true;
}

function handleAuthClick() {
    tokenClient.callback = async (resp) => {
        if (resp.error !== undefined) {
            throw (resp);
        }
        document.getElementById('signout_button').style.visibility = 'visible';
        document.getElementById('authorize_button').innerText = 'Refresh';
        console.log('User signed in:', resp);
        // Continue with your logic after sign-in
    };

    if (gapi.client.getToken() === null) {
        // Prompt the user to select a Google Account and ask for consent to share their data
        // when establishing a new session.
        tokenClient.requestAccessToken({ prompt: 'consent' });
    } else {
        // Skip display of account chooser and consent dialog for an existing session.
        tokenClient.requestAccessToken({ prompt: '' });
    }
}

function handleSignoutClick() {
    const token = gapi.client.getToken();
    if (token !== null) {
        google.accounts.oauth2.revoke(token.access_token);
        gapi.client.setToken('');
        document.getElementById('authorize_button').innerText = 'Authorize';
        document.getElementById('signout_button').style.visibility = 'hidden';
    }
}

function fetchStudentIDs() {
// Define the name of the sheet
const sheetName = 'studentList';

// Clear the detentionStudents array if it's not empty
detentionStudents.length = 0;

// Load the Google Sheets API
gapi.load('client', initClient);

function initClient() {
    gapi.client.init({
        apiKey: API_KEY,
        discoveryDocs: 
['https://sheets.googleapis.com/$discovery/rest?version=v4'],
    }).then(function () {
        // Fetch the data from the sheet
        gapi.client.sheets.spreadsheets.values.get({
            spreadsheetId: SHEET_ID,
            range: `${sheetName}!A:A`,
        }).then(function (response) {
            const values = response.result.values;
            if (values && values.length > 0) {
                // Extract user IDs and store them in the detentionStudents array
                detentionStudents = values.map(row => row[0]);
                console.log('Student IDs retrieved:', detentionStudents);
                confirm('Student IDs retrieved:' + detentionStudents)
            } else {
                console.log('No data found in the sheet.');
            }
        }, function (error) {
            console.error('Error fetching data from the sheet:', error);
        });
    });
}
}



function submitDetentionRecord() {
    const rowData = ["John Doe", "today", "30", "tardy", "1st Period"];

    // Append values to the "detentionRecords" sheet
    appendValues(SHEET_ID, 'detentionRecords', 'RAW', [rowData], (response) => {
        console.log('Detention record submitted to Google Sheet:', response);
        // Optionally, you can clear the form or perform other actions after submission.
        alert(`The detention for ${currentStudent} was submitted`);
    });
}

function appendValues(spreadsheetId, range, valueInputOption, _values, callback) {
    let values = _values;
    const body = {
        values: values,
    };
    try {
        gapi.client.sheets.spreadsheets.values.append({
            spreadsheetId: spreadsheetId,
            range: range,
            valueInputOption: valueInputOption,
            resource: body,
        }).then((response) => {
            const result = response.result;
            console.log(`${result.updates.updatedCells} cells appended.`);
            if (callback) callback(response);
        });
    } catch (err) {
        console.error('Error appending values to Google Sheet:', err);
        alert('There was an error submitting the detention');
    }
}

// Attach the submitDetentionRecord function to the submit button

 
 
 
 document.getElementById('submitDetention').addEventListener('click', submitDetentionRecord);

</script>


<script async defer src="https://apis.google.com/js/api.js" onload="gapiLoaded()"></script>
<script async defer src="https://accounts.google.com/gsi/client" onload="gisLoaded()"></script>
</body>
</html>

排查与修复步骤 #

1. 确认权限与令牌绑定 #

• 检查授权回调返回的权限范围：在handleAuthClick的回调里添加console.log('Granted scopes:', resp.scope)，确认包含https://www.googleapis.com/auth/spreadsheets。
• 授权成功后必须将令牌绑定到gapi客户端，否则写入请求会默认用API_KEY（仅只读）。修改handleAuthClick的回调：

tokenClient.callback = async (resp) => {
    if (resp.error !== undefined) {
        throw (resp);
    }
    // 新增：将授权令牌绑定到gapi客户端
    gapi.client.setToken({access_token: resp.access_token});
    document.getElementById('signout_button').style.visibility = 'visible';
    document.getElementById('authorize_button').innerText = 'Refresh';
    console.log('User signed in:', resp);
    console.log('Granted scopes:', resp.scope);
};

2. 修复写入请求的错误处理 #

原代码的try/catch无法捕获Promise异步错误，改用.catch()处理：

function appendValues(spreadsheetId, range, valueInputOption, _values, callback) {
    let values = _values;
    const body = {
        values: values,
    };
    gapi.client.sheets.spreadsheets.values.append({
        spreadsheetId: spreadsheetId,
        range: range,
        valueInputOption: valueInputOption,
        resource: body,
    }).then((response) => {
        const result = response.result;
        console.log(`${result.updates.updatedCells} cells appended.`);
        if (callback) callback(response);
    }).catch((err) => {
        console.error('Error appending values to Google Sheet:', err);
        alert('提交失败：' + err.message);
    });
}

3. 修正代码中的变量错误 #

submitDetentionRecord里的currentStudent未定义，会导致alert报错，替换为实际值：

alert(`The detention for John Doe was submitted`);

4. 核对控制台配置 #

• 确认OAuth客户端ID的重定向URI和代码中的redirectUri完全一致（包括协议、域名、路径）。
• 确认Google Sheets API已在控制台启用。
• 如果应用未发布，检查测试用户是否已添加到OAuth同意屏幕的测试列表中。

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