如何在 Google 电子表格中插入列？

我想将新数据添加到工作表的开头（开头）。所以我必须在工作表中添加一个新的 A1 列。但我找不到任何 PHP 的 API 示例。

现在我用这个附加数据：

$body   = new Google_Service_Sheets_ValueRange(['values' => $values]);
$result = $service->spreadsheets_values->append($new_sheet_id, 'A1:E1', $body, $options); // appent the data to the spreadsheet

Thanks.

更新： 这是我发现的

/* insert columns */
$requests = new Google_Service_Sheets_Request(array(
    'insertDimension' => array(
        'range' => array(
            'sheetId' => 0,
            'dimension' => "COLUMNS",
            'startIndex' => 0,
            'endIndex' => 5
        )
    )
));
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest(array(
    'requests' => $requests
));
$result = $service->spreadsheets->batchUpdate($new_sheet_id, $batchUpdateRequest);
/* insert columns */

我相信电子表格 API 不可能做到这一点。我一直在研究这种可能性，但到目前为止我还不知道如何做到这一点。有一个替代解决方案，但要求您使用Google Apps 脚本执行 API https://developers.google.com/apps-script/guides/rest/api。如果解决方案合适，请随时实施。按着这些次序：

1. 创建一个新的电子表格并将其命名为您想要的名称。
2. Fill out the spreadsheet so it looks the same as the image below
3. 转到“工具 > 脚本编辑器”打开脚本编辑器
4. Name the project anything you want as you see in the image below

5. 删除“Code.gs”中的所有内容并粘贴以下内容：

   function insertColumn(vals) {
   
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheets()[0];
   
       // This inserts a column in the first column position
       sheet.insertColumnBefore(1);  
   
       //This set the values in the cells of the first column inserted
       var range = sheet.getRange("A1:A5");
       range.setValues(vals);
   
   }
   

6. Now, go to the API manager, select your project and enable the API, like in the image below

7. Go to your project settings and copy the Project Number, as in the image below

8. 返回到步骤 3 中说明并显示于步骤 4 中的脚本编辑器。单击“资源 > 云平台项目”

9. Paste the project number in the field that reads "Enter Project Number here" and then click on "Set Project". See image below.

10. 您将看到一个确认窗口。确认更改并等待其完成。

11. Inside the project editor, go to "Publish > Deploy as API Executable". Type a version and click on "Deploy". See image below

12. It will show you a couple more of info messages and then you should see this:

13. 请复制当前API ID然后确保保存项目更改。

14. 创建一个新的 PHP 文件并粘贴以下内容：

    <?php session_start(); 
    
    //INCLUDE PHP CLIENT LIBRARY
    require_once 'vendor/autoload.php';
    
    //scope required to modify the spreadsheet
    $scopes = array("https://www.googleapis.com/auth/spreadsheets");
    
    // Create client object
    $client = new Google_Client(); 
    $client->setRedirectUri('http://'.$_SERVER['HTTP_HOST'].'/index.php');
    $client->setAuthConfig("client_secret.json");
    $client->setScopes($scopes);
    
    if( isset($_SESSION["access_token"]) && ($_SESSION["access_token"]) ) {
    
        $client->setAccessToken($_SESSION["access_token"]);    
        $service = new Google_Service_Script($client);
    
        //Here goes the script Id you copied on step 13
        $scriptId = 'XXX-OROcbUXO78URUxxxLYqFdOk6teXXX';
    
        $values = array( 
            array("Email"),
            array("[email protected] /cdn-cgi/l/email-protection"),
            array("[email protected] /cdn-cgi/l/email-protection"),
            array("[email protected] /cdn-cgi/l/email-protection"),
            array("[email protected] /cdn-cgi/l/email-protection")
        );
    
        $params = array($values);
    
        // Create an execution request object.
        $request = new Google_Service_Script_ExecutionRequest();
        $request->setFunction('insertColumn');
        $request->setParameters($params);
        $request->setDevMode(true); //required to work with parameters
    
        try {
        // Make the API request.
            $response = $service->scripts->run($scriptId, $request);
    
            if ($response->getError()) {
            // The API executed, but the script returned an error.
    
            // Extract the first (and only) set of error details. 
            // The values of this object are the script's 'errorMessage'
            // and 'errorType', and an array of stack trace elements.
            $error = $response->getError()['details'][0];
            printf("Script error message: %s\n", $error['errorMessage']);
    
                if (array_key_exists('scriptStackTraceElements', $error)) {
                    // There may not be a stacktrace if the script didn't start executing.
                    print "Script error stacktrace:\n";
                    foreach($error['scriptStackTraceElements'] as $trace) {
                        printf("\t%s: %d\n", $trace['function'], $trace['lineNumber']);
                    }
                }
    
            } else {
            // The structure of the result will depend upon what the Apps Script
            // function returns.
            $resp = $response->getResponse();    
            var_dump($resp);          
    
            }
        } catch (Exception $e) {
            // The API encountered a problem before the script started executing.
            echo 'Caught exception: ', $e->getMessage(), "\n";
        }    
    
    } else {
    
        if( !isset($_GET["code"]) ){
    
            $authUrl = $client->createAuthUrl();
            header('Location: ' . filter_var($authUrl, FILTER_SANITIZE_URL));
    
        } else {
    
            $client->authenticate($_GET['code']);
            $_SESSION['access_token'] = $client->getAccessToken();
    
            $redirect_uri = 'http://'.$_SERVER['HTTP_HOST'] .'/index.php';
            header('Location: ' . filter_var($redirect_uri, FILTER_SANITIZE_URL));
    
        }
    
    }
    
    ?>
    

15. Run the PHP script and then look at the result in your Spreadhsheet. You should see as below.

如您所见，在开头插入了一列，并且也填写了值。我希望这对您或其他人有所帮助。