Google spreadsheet API how to insert new row

Question

So far I've only used the API to retrieve data from a Google spreadsheet, but I'm not sure how to insert data. I've looked all over but none of the examples are clear enough.

For retrieving data, all I had to do was construct a URL and retrieve it using CURL in PHP like this:

    //Get spreadsheet data
    $headers = array(
        "Authorization: GoogleLogin auth=" . $auth,
        "GData-Version: 3.0",
        );
    $curl = curl_init();
    curl_setopt($curl, CURLOPT_URL, "https://spreadsheets.google.com/tq?tqx=out:html&tq=select%20D%2C%20E%20where%20B%3D&key=1c1xxxxxxxxxxxxx                                                                                                                                           
    curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
    curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);
    curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1
    $response= curl_exec($curl);

, how do I construct a similar URL to insert? I would think there would be a similar URL that can be used. Can someone please point me in the right direction? I could not find info about it here https://developers.google.com/chart/interactive/docs/querylanguage

user2029890 user2029890 · Accepted Answer · 2014-04-25T18:04:33

Well, no answers but some more research pointed me to https://developers.google.com/google-apps/spreadsheets/#working_with_list-based_feeds where clicking under the 'protocol' link of 'adding a list row' gave me enough clues to construct the following:

//create xml with each element representing a column header (note: for some reason the headers must only contain alphanumeric characters)
$xml = "
<entry xmlns='http://www.w3.org/2005/Atom'
    xmlns:gsx='http://schemas.google.com/spreadsheets/2006/extended'>
  <gsx:id>123</gsx:id>
  <gsx:status>123</gsx:status>
  <gsx:date>4/26/2014</gsx:date>
  <gsx:user>bob</gsx:user>
</entry>";

//set headers which includes auth from elsewhere in the code
$headers = array(
    "Authorization: GoogleLogin auth=" . $auth,
    "GData-Version: 3.0",
    "Content-Type: application/atom+xml",
    );

//post the xml. The key in the url is taken from the actual url when you view the sheet 
$curl = curl_init();
curl_setopt($curl, CURLOPT_POST, 1);
curl_setopt($curl, CURLOPT_URL, "https://spreadsheets.google.com/feeds/list/xxxxxxxxxxxxxxxkeyxxxxxxxxx/0/private/full");
curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
curl_setopt($curl, CURLOPT_POSTFIELDS, "$xml");
curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
$response = curl_exec($curl);

Hopefully this helps someone else

Google spreadsheet API how to insert new row

2 Answers