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
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>';
......
I believe your goal is as follows.
{-code-1}
. When the value of column "A" is{-code-1}
, you don't want to retrieve the values after this line.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?
$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));
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 is1
. 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?
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];
foreach($values as $val) {
if (count($val) > 0 && $val[0] == {-code-1}) {
break;
}
list($firstname, {-code-11}, $address, $city, $state, {-code-12}) = $val;
}
or
$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;
}
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Find the answer in similar questions on our website.
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.
PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites.
The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/
Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.
Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.
Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.