通过 cURL/PHP 将新行插入 Google 电子表格 - 如何? [英] Insert new rows into Google Spreadsheet via cURL/PHP - HOW?

问题描述

是否有关于如何执行此操作的脚本或教程?如何实现将数据从我的 PHP 应用程序插入到 Google 电子表格中?

Is there a script or tutorial on how to do this? How to implement inserting of data from my PHP application into a Google Spreadsheet?

我查看了 API (https://developers.google.com/google-apps/spreadsheets/) 并且没有 PHP 代码/示例.

I've looked at the API (https://developers.google.com/google-apps/spreadsheets/) and there are no PHP codes/examples.

我已经尝试过 Zend 库的实现，但这种方法似乎已经过时了，因为我在简单的行插入中遇到了一些愚蠢的错误.

I've tried the Zend Library implementation, but something seems very outdated with that approach as I am getting some silly errors on a simple row insert.

我认为我无法使用 Google Apps 脚本 (https://developer.google.com/apps-script/reference/spreadsheet/)，因为我不知道如何从 PHP 脚本编码和触发.

I don't think I can use the Google Apps Script (https://developers.google.com/apps-script/reference/spreadsheet/) as I don't see how I can code and trigger from a PHP script.

还有什么吗?

难道没有我们可以轻松使用和利用的可行 API 吗?

Isn't there a workable API for this that we can easily use and tap into?

推荐答案

我不认为我可以使用 Google Apps 脚本，因为我不知道如何从 PHP 脚本进行编码和触发

I don't think I can use the Google Apps Script as I don't see how I can code and trigger from a PHP script

当然可以.您可以使用 Google Apps 脚本创建网络服务，该服务可以接收和处理服务请求.将参数传递给 Web 服务很简单，例如可以使用 cURL 来完成.

Sure you can. You can create a web service using Google Apps Script, that can receive and process service requests. It's simple to pass parameters to the web service, this could be done using cURL, for instance.

以下示例适用于包含两列的简单电子表格.您可以通过以下方式添加更多行:

The following example is for a simple spreadsheet with two columns. You can add further rows by:

curl -L <SCRIPT URL>?col1='Value for column 1'&col2='Another value'

或通过浏览器网址:

https://<SCRIPT URL>?col1='Value for column 1'&col2='Another value'

此示例电子表格发布在此处，下面是可用于添加行的示例 URL:

This example spreadsheet is published here, and below is an example URL that can be used to add a row:

https://script.google.com/macros/s/AKfycbzVDFmOeaQc4mDZhWCnwf0CUnX64YNhhnKIlTYhqtpBraINUf9e/exec?col1='Value for column 1'&col2='Another value'

说明

这是一步一步的.

Instructions

Here is a step-by-step.

1. 打开 Google 电子表格，确保电子表格可完全访问(公开)
2. 工具 --> 脚本编辑器
3. 复制下面的代码并粘贴到编辑器中
4. 将 --spreadsheet-id-- 替换为您的工作表 ID(很容易找到)
5. 文件 --> 保存
6. 发布 --> 部署为 web-app --> 版本 = NEW ;根据需要设置访问权限
7. 授权(将提示您输入凭据)--> 部署
8. 您现在将获得脚本的 URL
9. 将 ?col1='Colum1Data'&col2='Colum2Data' 附加到 URL 或根据需要进行编辑以将数据从 URL 推送到电子表格
10. 完成

1. Open a Google spreadsheet, ensure that the spreadsheet is fully accessible (public)
2. Tools --> Script Editor
3. Copy code below and paste into editor
4. Replace the --spreadsheet-id-- with your sheet's id (easy to find this)
5. File --> Save
6. Publish --> Deploy as web-app --> Version = NEW ; set access as needed
7. Authorize (will prompt you for creds) --> DEPLOY
8. You will now get a URL to the script
9. Append ?col1='Colum1Data'&col2='Colum2Data' to the URL or edit as needed to push data to the spreadsheet from the URL
10. DONE

(感谢 vr00n.)

(Thanks to vr00n.)

这可能是一个独立的或容器绑定的脚本；无论哪种方式，您都需要提供电子表格的 ID，因为 Web 应用程序不在电子表格上下文中运行.它不显示 html，而是使用 ContentService 来提供简单的文本 - 您可以在任何适合您的应用程序的方式.

This could be a stand-alone or container-bound script; either way, you need to supply the ID of the spreadsheet, since the web app does not run within a spreadsheet context. Rather than display html, it uses the ContentService to serve simple text - you can elaborate that in any way that is appropriate for your application.

function doGet(e) {  
  Logger.log( JSON.stringify(e) );  // view parameters

  var result = 'Ok'; // assume success

  if (e.parameter == undefined) {
    result = 'No Parameters';
  }
  else {
    var id = '--spreadsheet-id--'; // Spreadsheet id
    var sheet = SpreadsheetApp.openById(id).getActiveSheet();
    var newRow = sheet.getLastRow() + 1;
    var rowData = [];
    for (var param in e.parameter) {
      Logger.log('In for loop, param='+param);
      var value = stripQuotes(e.parameter[param]);
      //Logger.log(param + ':' + e.parameter[param]);
      switch (param) {
        case 'col1': 
          rowData[0] = value;
          break;
        case 'col2':
          rowData[1] = value;
          break;
        default:
          result = "unsupported parameter";
      }
    }
    Logger.log(JSON.stringify(rowData));

    // Write new row to spreadsheet
    var newRange = sheet.getRange(newRow, 1, 1, rowData.length);
    newRange.setValues([rowData]);
  }

  // Return result of operation
  return ContentService.createTextOutput(result);
}

/**
 * Remove leading and trailing single or double quotes
 */
function stripQuotes( value ) {
  return value.replace(/^["']|['"]$/g, "");
}

您应该阅读 Google 的内容服务文档，尤其是有关重定向和敏感信息的注意事项.

You should read over Google's Content Service documentation, especially the cautions around redirection, and sensitive information.

这篇关于通过 cURL/PHP 将新行插入 Google 电子表格 - 如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！