使用PHP将数据添加到Google电子表格

Question

I'm trying to create an automation script with php which adds a new row of information to a google sheet that I've shared. I've been scouring tutorials all morning trying to figure this out but each method is different and doesn't seem to be exactly what I need to do. Essentially this script will be triggers when a form is submitted on my site. Its going to take the form data and drop is into my google sheet.

I've created a service account under my project, which is stored inside the json file that was downloaded. Here's the script I have now. It's giving me the following error

{ "error": { 
  "code": 403, 
  "message": "The caller does not have permission", 
  "errors": [ { 
    "message": "The caller does not have permission", 
    "domain": "global", 
    "reason": "forbidden" 
  } ], 
  "status": "PERMISSION_DENIED"
}

and here's the code I'm running. (yes its incomplete because I can't get past the auth)

public function index()
{
    $this->load->helper("url");
    require APPPATH . 'third_party/GoogleAPI/vendor/autoload.php';


    putenv('GOOGLE_APPLICATION_CREDENTIALS=' . APPPATH . 'third_party/GoogleAPI/bdgsheets.json');
    $client = new Google_Client;
    $client->useApplicationDefaultCredentials();

    $client->setApplicationName("Is this working");
    $client->setScopes(['https://www.googleapis.com/auth/drive','https://spreadsheets.google.com/feeds']);
    $client->setAccessType('offline');

    if ($client->isAccessTokenExpired()) {
        $client->refreshTokenWithAssertion();
    }

    $accessToken = $client->fetchAccessTokenWithAssertion()["access_token"];

    $sheets = new Google_Service_Sheets($client);

    $data = [];
    $current_row = 2;
    $spreadsheet_id = "{my spreadsheet id here}";
    $range = 'A1:H';
    $rows = $sheets->spreadsheets_values->get($spreadsheet_id, $range, ['majorDimension' => 'ROWS']);
}

Thanks in advance for the help

Answer 1

Have you put the right scope when adding creating the access token. If you had used the scope value

"https://www.googleapis.com/auth/spreadsheets.readonly" instead of

"https://www.googleapis.com/auth/spreadsheets" you won't have write permission to the google sheet.

If you can share the code piece which creates the bdgsheets.json file it is easy to understand whether you have the "write" permission.

Also, try changing this piece of code

$client->setScopes(['https://www.googleapis.com/auth/drive','https://spreadsheets.google.com/feeds']);

to

$client->setScopes(['https://www.googleapis.com/auth/spreadsheets']);

and give it a shot