我可以将Google电子表格用作我的PHP应用程序的数据库吗？

Question

I have database Google spreadsheet. How can I process my database with PHP or any programming language? Do I need to move the database to other database? For example: MySQL

Answer 1

I recommend you move to a MySQL database, especially for PHP, they work very well, and I think it's more reliable - Google has a few weird quirks with their services that can be a pain (such as a cap into data flow, which can become very inconvenient, or expensive). Also if your host is running on Apache, MySQL runs very well on there without any need to manually setup those services. No need to outsource.

If you go with MySQL, you are looking into a very simple (and Object-Oriented Programming or Procedural method) interface. And you can just import the current data you have in your Google Spreadsheet to a MySQL database using a csv file.

For example, your connection would look like:

OOP:

$_connection = new mysqli(host, user, password, database);

Procedural:

$_connection = mysqli_connect(host, user, password, database);

Then you can SELECT values from the database using simple queries,:

OOP:

$sql = "SELECT row1, row2, row3 FROM table";

if ($result = $_connection->query( $sql ))
{
    if ($result->num_rows > 0)
    {
        while ($row = $result->fetch_assoc())
        {
            //... get using the associate method
            echo $row['row1'];
            // etc...
        }
    }
}

Procedural:

$sql = "SELECT row1, row2, row3 FROM table";
$result = mysqli_query($_connection, $sql);
$row = mysqli_fetch_assoc($result);
echo $row['row1'];

So indeed my recommendation is for you to start using MySQL. It is simple, there's a lot of documentation on it.

If you are interested, you can find the official PHP documentation from here.

If you still want to use Google Spreadsheets, continue reading and you will find out more.

---

However, as you said any programming language I recommend looking at Python. If you have any Python proficiency, or you are willing to learn it, you could you this tutorial from Twilio as a jump start into that direction you are trying to get.

The tutorial will walk you through what you need and how to get those depencies, but a quick overview:

You need gspread as well as oauth2client from Google's APIs. It is a simple command - assuming you have pip:

pip install gspread oauth2client

You need them as per Twilio:

1. oauth2client – to authorize with the Google Drive API using OAuth 2.0
2. gspread – to interact with Google Spreadsheets (if you are serious about using gspread, check out their documentation, they are pretty throughout)

Once you have that installed you can move on to getting the right authentication from Google, you will be using the Google Drive API. Twilio shows you how to do all that.

Assuming you have the right dependencies, and you have authorized your app using Google Drive API, you are ready to get to the code:

import gspread
from oauth2client.service_account import ServiceAccountCredentials


# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)

# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
sheet = client.open("Copy of Legislators 2017").sheet1

# Extract and print all of the values
list_of_hashes = sheet.get_all_records()
print(list_of_hashes)