Insert new rows into Google Spreadsheet via cURL/P

Question

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?

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

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.

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.

Anything else?

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

Answer 1

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

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'

or by browser URL:

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

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

(Thanks to vr00n.)

Code.gs

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, "");
}

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