Google Sheet PHP API v.4: how can I add a "stop" row in a Sheet?

View Original

I'm not sure of the correct terminology to use, but what I need to do is add a row in a Google Sheet that "stops" the PHP API v.4 from getting any more data from the sheet.

I have a Sheet with hundreds of rows of data that I am getting via the API, and below the rows I want are some formula fields that I don't want (and I can't change their location in the Sheet or delete them.)

How can I add something like "99999" to the A cell in a row that tells the API to stop at the row before it?

Or is there a better way to tell the API to stop?

This is what I'm using in the Sheets v.4 PHP API to get rows from the sheet. The data is delimited by newlines.

// authentication not shown
$range = 'Sheet3!AD2:Z';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

// This explode strings into array
foreach($values as $val) {
$valuearray = explode("", implode($val));

// This assigns variables that are used to echo the data
$firstname = $valuearray[0];
$lastname = $valuearray[1];
$address = $valuearray[2];
$city = $valuearray[3];
$state = $valuearray[4];
$zip = $valuearray[5];

That gives me an output like this:

JoeSmith123 Dogpatch LaneDog TownAlabama34567

JohnJones456 Cat HollowCat TownArkansas12345

and on and on...

What I need to do is add a row with something - like 999999 - in an A cell that stops the API on the previous row.

Update 12/15/21

Answer

I must have been overthinking it; this works and is as simple as possible:

// This explode strings into array
foreach($values as $val) {
$valuearray = explode("\n", implode($val));

// This assigns variables that are used to echo the data
$firstname = $valuearray[0];
$lastname = $valuearray[1];
$address = $valuearray[2];
$city = $valuearray[3];
$state = $valuearray[4];
$zip = $valuearray[5];

if($firstname==='999999') { break; } // Break on 999999 in the firstname field

echo '<h1 class="firstname">' . $firstname . '</h1>';
......

Answer

Solution:

I believe your goal is as follows.

You want to retrieve the values by checking whether the value of column "A" is {-code-1}. When the value of column "A" is {-code-1}, you don't want to retrieve the values after this line.
You want to achieve this using googleapis for php.

Unfortunately, in the current stage, I think that your goal cannot be directly achieved using Sheets API. So, in this case, how about the following sample script?

Sample script:

$range = 'Sheet3!AD2:Z';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);

// --- I added below script.
$v = $response['values'];
$values = [];
for($i = 0; $i <= count($v); $i++){
    if (strval($v[$i][0]) == "{-code-1}") {
        break;
    }
    array_push($values, $v[$i]);
}
// ---

// This explode strings into array
foreach($values as $val) {
$valuearray =explode("\n", implode($val));

By this, the values can be retrieved by checking the value of column "A".

Reference:

Method: spreadsheets.values.get

Added:

About your following updated script,

// authentication not shown
$range = 'Sheet3!AD2:Z';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

// This explode strings into array
foreach($values as $val) {
$valuearray = explode("\n", implode($val));

// This assigns variables that are used to echo the data
$firstname = $valuearray[0];
$lastname = $valuearray[1];
$address = $valuearray[2];
$city = $valuearray[3];
$state = $valuearray[4];
$zip = $valuearray[5];

In your script, $values is 2 dimensional array. And when your sample Spreadsheet of the image is used, it's like [["Joe","Null","blah blah","Smith","123 Dogpatch Lane","Dog Town","Alabama",34567],,,]. So when $valuearray = explode("\n", implode($val)); is run, ["Joe","Null","blah blah","Smith","123 Dogpatch Lane","Dog Town","Alabama",34567] is converted to ["JoeNullblah blahSmith123 Dogpatch LaneDog TownAlabama34567"]. By this, {-code-10} occurs no error. But {-code-11} to {-code-12} occurs an error. Because the array length is 1. I thought that this might be the reason of your issue. If you want to put each value to $firstname to {-code-12}, how about the following modification?

From:

foreach($values as $val) {
$valuearray = explode("\n", implode($val));

// This assigns variables that are used to echo the data
{-code-10};
{-code-11} = $valuearray[1];
$address = $valuearray[2];
$city = $valuearray[3];
$state = $valuearray[4];
{-code-12} = $valuearray[5];

To:

foreach($values as $val) {
    if (count($val) > 0 && $val[0] == {-code-1}) {
        break;
    }
    list($firstname, {-code-11}, $address, $city, $state, {-code-12}) = $val;
}

$v = $values;
$values = [];
for($i = 0; $i <= count($v); $i++){
    if (count($v[$i]) > 0 && $v[$i][0] == {-code-1}) {
            break;
    }
    array_push($values, $v[$i]);
}
foreach($values as $val) {
    list($firstname, {-code-11}, $address, $city, $state, {-code-12}) = $val;
}

Source