如何用PHP和SheetDB在插入数据前检查Google Sheets中是否存在指定号码

一、插入前检查电话号码是否存在 #

SheetDB提供查询接口，可通过指定字段值检索数据。我们先发送GET请求查询目标电话号码是否已存在：

1. 构造查询URL：在原API端点后拼接查询参数，格式为/search?number={电话号码}
2. 发送GET请求获取匹配结果
3. 若返回结果数组非空，说明号码已存在，终止插入流程

二、处理API响应判断插入成功 #

SheetDB插入成功的核心标志是HTTP状态码201，同时响应体返回{"created": 1}；不同错误状态码对应不同问题，可针对性处理。

修改后的完整脚本 #

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $api_key = '...';
    $api_endpoint = '...';
    $target_number = $_POST['number'];

    // 1. 检查电话号码是否重复
    $check_url = $api_endpoint . '/search?number=' . urlencode($target_number);
    $check_headers = [
        'Authorization: Bearer ' . $api_key,
    ];

    $ch = curl_init($check_url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $check_headers);
    $check_response = curl_exec($ch);
    $check_http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    if ($check_http_code === 200) {
        $existing_data = json_decode($check_response, true);
        if (!empty($existing_data)) {
            echo "该电话号码已提交过，请勿重复提交";
            exit;
        }
    } else {
        echo "检查重复数据时出错，请稍后重试";
        exit;
    }

    // 2. 准备插入数据
    $studyField = $_POST['study-field'];
    $data = [
        'name' => $_POST['name'],
        'email' => $_POST['email'],
        'number' => $target_number,
        'phone' => $_POST['phone'],
        'softwares' => $_POST['softwares'],
        'usthb_student' => $_POST['usthb_student'],
        'university_name' => isset($_POST['university_name']) ? $_POST['university_name'] : '',
        'study_field' => $studyField === 'other' ? $_POST['other-f'] : $studyField,
        'message' => $_POST['message'],
    ];

    // 3. 执行插入请求
    $headers = [
        'Content-Type: application/json',
        'Authorization: Bearer ' . $api_key,
    ];

    $ch = curl_init($api_endpoint);
    curl_setopt($ch, CURLOPT_POST, 1);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode(['data' => $data]));
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
    $response = curl_exec($ch);
    $insert_http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);

    // 4. 判断插入结果
    if ($insert_http_code === 201) {
        $insert_result = json_decode($response, true);
        if (isset($insert_result['created']) && $insert_result['created'] === 1) {
            echo "数据提交成功！";
        } else {
            echo "数据提交异常，请稍后重试";
        }
    } else {
        switch ($insert_http_code) {
            case 400:
                echo "提交数据格式错误，请检查输入";
                break;
            case 401:
                echo "API密钥无效，请核对";
                break;
            case 404:
                echo "SheetDB端点地址错误";
                break;
            default:
                echo "提交失败，错误码：" . $insert_http_code;
        }
    }
}

关键注意事项 #

• 重复检查时用urlencode()处理电话号码，避免特殊字符导致查询失败；若Sheet中电话号码列的表头不是number，需替换为实际表头名称。
• 优先通过HTTP状态码判断结果，响应体仅作为辅助验证，确保逻辑可靠性。

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