0

First time trying to automate some tasks with PHP and gSheet with API. Sorry in advance if my question is fool, I'm really lost here as a newbie.

My php page gets an array converted from a csv file like the example:

0,,0,R0VXJ9I7,2,*%*
1,,0,R0VXJ9I7,3,*%*
,,,1,,*%*

My goal is to append data in a gsheet file as shown above. Each "," means a different column, each ",%" indicates a new row. I got all done but there is one problem: in the Google sheet file the next lane appended column starts where the previous lane finished, like for example:

0  |   |0  |R0VXJ9I7   |2
                         1  |   |0  |R0VXJ9I7   |3
                                                  | | |1|

What am I doing wrong? I would appreciate any help or advice

require '../../data/vendor/autoload.php';
//                       the string comes here as plain text
$oddRow = explode(',*%*',$vl02);
//only for debug @.@
$i = 0;
foreach($oddRow as $preRow){
   $client = new \Google_Client();
   $client->setApplicationName('Google Sheets API');
   $client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
   $client->setAccessType('offline');
   $path = '../../data/credentials.json';
   $client->setAuthConfig($path);
   $service = new \Google_Service_Sheets($client);
   $spreadsheetId = '#####';//protected for safety
   $spreadsheet = $service->spreadsheets->get($spreadsheetId);
        
   $newRow = explode(',', $preRow);
   echo $newRow[$i];   //debug
   echo $i;            //debug
   $i++;               //debug  
   $rows = [$newRow];
            
   $valueRange = new \Google_Service_Sheets_ValueRange();
   $valueRange->setValues($rows);
   $range = '####';//protected for safety 
   $options = ['valueInputOption' => 'USER_ENTERED'];
   $service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $options);    
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ja rpa
  • 1
  • 1
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand the relationship between `an array converted from a csv file like the example:` and your showing script. Can I ask you about the detail of it? – Tanaike Apr 06 '23 at 07:53
  • looks like you need to get the column back to 0 for each new row – David Marquardt Apr 06 '23 at 07:53
  • @Tanaike The data I want to append in google sheet come from a csv file. That means I have a big string wich I have to split in lines and then in columns – ja rpa Apr 06 '23 at 08:01
  • @DavidMarquardt thanks for the tip, let me work on it! – ja rpa Apr 06 '23 at 08:02
  • Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, from your reply, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill. – Tanaike Apr 06 '23 at 08:06
  • @Tanaike Every new line added on google sheet starts in the last column of the previous line: [code block] AAAAAAAA -------------BBBBBBBBBB ---------------------------CCCCCCCCCCCC ---------------------------------------------DDDDDDDDD[/code block] – ja rpa Apr 06 '23 at 08:24
  • Thank you for replying. I would like to try to correctly understand your reply. When I could correctly understand it, I would like to think of a solution. – Tanaike Apr 06 '23 at 08:36

1 Answers1

0

Here is how I Got things done:

//created by Artur Morais
require '../../data/vendor/autoload.php';
$oddRow = explode(',*%*',$vl02);
$client = new \Google_Client();
$client->setApplicationName('Google Sheets API');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS])
$client->setAccessType('offline');
$path = '../../data/credentials.json';
$client->setAuthConfig($path);
$service = new \Google_Service_Sheets($client);
$spreadsheetId = '####';
$spreadsheet = $service->spreadsheets->get($spreadsheetId);
$range = 'sheet1';   //here we get the Range A[last row] once
foreach($oddRow as $preRow){
    $newRow = explode(',', $preRow);
    $rows = [$newRow];
    $valueRange = new \Google_Service_Sheets_ValueRange();
    $valueRange->setValues($rows);
    $options = ['valueInputOption' => 'USER_ENTERED'];
    $service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $options); 

    //this block is fixing the range problem
    $response   = $service->spreadsheets_values->get( $spreadsheetId, $range );//new consult on excel
    $get_values = $response->getValues();  //set
    $bloopRow   = count( $get_values ) + 1;//add 1 to number of rows
    $range = 'sheet1!A' . $bloopRow;  //fix variable range to the column A from the next free lane
    unset($bloopRow );                     //just in case. Why not?
    unset($newRow);
    unset($rows);
}

Hope this could help someone. Thanks you all folks for helping me out solving this problem.

ja rpa
  • 1
  • 1