title: Append row in a spreadsheet as the values were user entered
body: <p>I have an array of strings and I need to append it in a spreadsheet, from the first to the n-th column according to the size of the array.</p>
  
  <p>The strings could represent several type of values, e.g. numbers, dates and so on, so they must be put in the sheet as user entered to keep their meaning.</p>
  
  <p>As far as I understand, the Google APIs offer two ways to append values, but none of them seems to be suitable for this case. Here's the issues I encountered:</p>
  
  <h2>Using <a href="https://developers.google.com/resources/api-libraries/documentation/sheets/v4/php/latest/class-Google_Service_Sheets_AppendCellsRequest.html" rel="noreferrer">Google_Service_Sheets_AppendCellsRequest</a></h2>
  
  <p>The problem with this method is that I cannot find a way to set the values as user entered. Here is a code sample:</p>
  
  <pre><code>  // Build the CellData array
    $values = array();
    foreach( $ary_values AS $d ) {
      $cellData = new Google_Service_Sheets_CellData();
      $value = new Google_Service_Sheets_ExtendedValue();
      $value-&gt;setStringValue($d);
      $cellData-&gt;setUserEnteredValue($value);
      $values[] = $cellData;
    }
    // Build the RowData
    $rowData = new Google_Service_Sheets_RowData();
    $rowData-&gt;setValues($values);
    // Prepare the request
    $append_request = new Google_Service_Sheets_AppendCellsRequest();
    $append_request-&gt;setSheetId(0);
    $append_request-&gt;setRows($rowData);
    $append_request-&gt;setFields('userEnteredValue');
    // Set the request
    $request = new Google_Service_Sheets_Request();
    $request-&gt;setAppendCells($append_request);
  </code></pre>
  
  <p><em>(full working code is <a href="https://gist.github.com/thehelvetian/2e94d60b796735b167dfb1c7560049ae" rel="noreferrer">here</a>)</em></p>
  
  <p>Even using <a href="https://developers.google.com/resources/api-libraries/documentation/sheets/v4/java/latest/com/google/api/services/sheets/v4/model/CellData.html#setUserEnteredValue(com.google.api.services.sheets.v4.model.ExtendedValue)" rel="noreferrer"><code>CellData.setUserEnteredValue</code></a>, the data is interpreted as string because one have to set the values using <a href="https://developers.google.com/resources/api-libraries/documentation/sheets/v4/php/latest/class-Google_Service_Sheets_ExtendedValue.html" rel="noreferrer"><code>ExtendedValue.setStringValue</code></a> (or the other supported methods, i.e. <code>setBoolValue</code>, <code>setFormulaValue</code>, <code>setNumberValue</code>).</p>
  
  <h2>Using <a href="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append" rel="noreferrer">spreadsheets.values.append</a></h2>
  
  <p>Using this method I can have the data rendered as user entered, but the "appending" is done in the wrong place in several cases. As stated in <a href="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append" rel="noreferrer">the documentation</a>:</p>
  
  <blockquote>
    <p>The input range is used to search for
    existing data and find a "table" within that range. Values will be
    appended to the next row of the table, starting with the first column
    of the table.</p>
  </blockquote>
  
  <p>So this method is meant to behave in a different way from the previous one, and that leads to problem for me.</p>
  
  <p>This is a piece of code that should do what I want:</p>
  
  <pre><code>  $body = new Google_Service_Sheets_ValueRange([
      'values' =&gt; [ $array_values ]
    ]);
    $params = [
      'valueInputOption' =&gt; 'USER_ENTERED'
    ];
    // the n-th column
    $end_column = chr( ord('A') + count($array_values));
    $result = $sheet_service-&gt;spreadsheets_values-&gt;append(
      $fileId,
      $sheetName . "!A1:" . $end_column . "1",
      $body,
      $params
    );
  </code></pre>
  
  <p>Indeed, it works fine but with some exceptions, e.g.:</p>
  
  <ul>
  <li>if the cell <code>A1</code> is empty, the <code>append</code> method will put the values starting at column 2 (see this <a href="https://developers.google.com/sheets/api/guides/values#appending_values" rel="noreferrer">example</a> to understand why).</li>
  <li>if I have some written rows, then an empty row, then other written rows, the <code>append</code> method will "fill the gap" instead of putting the values after the very last written row.</li>
  </ul>
  
  <hr>
  
  <p>Am I doing some errors? Is there a way to make what I want using either one or the other method?</p>
  
  <p>To recap:</p>
  
  <ul>
  <li>the values must be considered as user entered (e.g. a number or a date should be interpreted as such)</li>
  <li>the values must be written in the row after the last non-empty cell, starting at column one</li>
  <li>the append operation must be atomic (i.e. sending one request to get the last non-empty cell row and then sending another request to write data into that is not an option, because in the meantime another user could put data there)</li>
  </ul>
  
  <p>Any advice is welcome!</p>