John Davidson

Message:

We have one requirement similar to google forms, which will send the data to google Sheets.

Using PHP we built a web app(which acts like a google form that collects data from users eg Name, Email, etc) that will send output data(submits Multiple output responses in a single submit click ie array of responses, eg. output.1:- Name: john, Email: [email protected], etc and output.2:- Name: sam, Email: [email protected], etc ) to the database, now the task is, we need to send the same data to google sheets using google sheets script URL instead of the database.

This is the code that we used to insert the web app's array of output data into the database

<?php 



insert.php



$connect = new PDO("mysql:host= localhost;dbname= ","user name","password");



$query = "

INSERT INTO expense 

(first_name, last_name, Category) 

VALUES (:first_name, :last_name, :Category)

";



for($count = 0; $count<count($_POST['hidden_first_name']); $count++)

{

    $data = array(

        ':first_name'   =>  $_POST['hidden_first_name'][$count],

        ':last_name'    =>  $_POST['hidden_last_name'][$count],

        ':Category' =>  $_POST['hidden_Category'][$count]

    );

    $statement = $connect->prepare($query);

    $statement->execute($data);

}



?>

So, we need the same kind of code which has to have the ability to send the obtained multiple output data ie array of responses (eg. output.1 : Name: john, Email: [email protected], etc and output.2: Name: sam, Email: [email protected], etc from single submit click) from the web app to google sheet.

---

My Answer:
protected], output.2:- Name: jane, Email: [email protected]) to google sheets.

To achieve this, we can use the Google Sheets API along with PHP to insert form data into a Google Sheet. Here is a step-by-step guide on how to do this:

1. Set up a Google Cloud Platform project and enable the Google Sheets API.
2. Create a service account and download the JSON key file.
3. Share the Google Sheet with the service account email address.
4. Install the Google API client library for PHP using Composer.
5. Use the following PHP code to insert form data into the Google Sheet:

php

require __DIR__ . '/vendor/autoload.php';

putenv('GOOGLE_APPLICATION_CREDENTIALS=/path/to/your/json/key/file.json');

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

$service = new Google_Service_Sheets($client);

$spreadsheetId = 'your_spreadsheet_id';
$range = 'Sheet1!A1:B';

$values = [
['Name', 'Email'],
['John', 'john@example.com'],
['Jane', 'jane@example.com'],
];

$body = new Google_Service_Sheets_ValueRange([
'values' => $values
]);

$params = [
'valueInputOption' => 'RAW'
];

$result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);

print_r($result);

?>


Replace `your_spreadsheet_id` with the ID of your Google Sheet and `/path/to/your/json/key/file.json` with the path to your JSON key file.

This code will insert the form data into the specified range in the Google Sheet. You can modify the `$values` array to include the form data submitted by the user.

Make sure to handle form submission in your PHP web app and call this code to insert the data into the Google Sheet.