• PHP 7.4 or higher
• Composer

cd /path-to-your/wp-content/plugins/
git clone git@github.com:sectsect/google-spreadsheet-to-db.git

cd google-spreadsheet-to-db
nano .gitignore

- vendor/

cd functions/composer/
composer install

By default, a new spreadsheet cannot be accessed via Google’s API. We’ll need to go to your Google APIs console and create a new project and set it up to expose your Spreadsheets’ data.

1. Go to the Google APIs Console.
2. Create a new project.
3. Click Enable API. Search for and enable the Google Sheets API.
4. Create credentials for a Web Server to access Application Data.
5. Name the service account and grant it a Project Role of Editor.
6. Download the JSON file.
7. Copy the JSON file to your app directory and rename it to client_secret.json
8. ⚠️ Set client_secret.json in a location to deny web access on your server.

We now have a big chunk of authentication information, including what Google calls a client_email, which uniquely represents this OAuth service account.
Grab the value of client_email from your client_secret.json, and head back to your spreadsheet. Click the Share button in the top right, and paste the client_email value into the field to give it edit rights.
Hit send. That’s it! 👌

1. Set the define() constants for client_secret.json in wp-config.php.

define( 'GOOGLE_SS2DB_CLIENT_SECRET_PATH', '/path/to/your/client_secret.json' );

2. Go to Settings -> Google Spreadsheet to DB on your WordPress Admin-Panel.
3. Set the following values and save it once.

• Data format to be stored in database
  • json_encode
  • json_encode (JSON_UNESCAPED_UNICODE)

4. Click the Import from Google Spreadsheet button. 🎉

• Spreadsheet ID
• Spreadsheet name (Optional)
• Single Sheet name
• Top Header Row
• Title (Optional)

You can edit the array got from Google API with add_filter( 'google_ss2db_before_save', $function_to_add ) in your functions.php before saving to database.

add_filter( 'google_ss2db_before_save', function ( $row, $worksheetid, $worksheetname, $sheetname ) {
  // Example
  if ( $worksheetname === 'My Spreadsheet' && $sheetname === 'Sheet1' ) {
    // Do something.

    return $something;
  }

  return $row;
}, 10, 3 );

And also use add_filter('google_ss2db_after_save', $return_array ) to perform any processing with the return value.

add_filter( 'google_ss2db_after_save', function ( $array ) {
  if ( 'My Spreadsheet' === $worksheetname ) {
    // $id              = $array['id'];
    // $date            = $array['date'];
    // $title           = $array['title'];
    // $value           = $array['value'];
    // $work_sheet_id   = $array['worksheet_id'];
    // $work_sheet_name = $array['worksheet_name'];
    // $sheet_name      = $array['sheet_name'];
    // $result          = $array['result'];
    
    // Do something...
    $return = $something;
  } else {
    $return = $array;
  }

  return $return;
} );

new Google_Spreadsheet_To_DB_Query();

$sheet = new Google_Spreadsheet_To_DB_Query();
$rows  = $sheet->getrow();
foreach ( $rows as $row ) {
  $id   = $row->id;
  $date = $row->date;
  $val  = json_decode( $row->value );
}

$args = array(
  'orderby' => 'id',
  'order'   => 'ASC',
  'limit'   => 3,
  'offset'  => 3,
);
$sheet = new Google_Spreadsheet_To_DB_Query( $args );
$rows  = $sheet->getrow();
foreach ( $rows as $row ) {
  $id   = $row->id;
  $date = $row->date;
  $val  = json_decode( $row->value );
}

$args = array(
  'where' => array(
    array(
      'key'   => 'id',
      'value' => 3,
    )
  ),
);

$args = array(
  'orderby' => 'id',
  'order'   => 'ASC',
  'limit'   => 3,
  'where'   => array(
    array(
      'key'     => 'worksheet_name',
      'value'   => 'My Spreadsheet',
      'compare' => '='
    ),
  ),
);

$args = array(
  'where' => array(
    array(
      'key'     => 'date',
      'value'   => '2020-08-01 12:34:56',
      'compare' => '>=',
    )
  ),
);

$args = array(
  'orderby' => 'id',
  'order'   => 'DESC',
  'limit'   => 10,
  'offset'  => 10,
  'where'   => array(
    'relation' => 'AND', // or 'OR'
    array(
      'key'     => 'date',
      'value'   => '2020-08-01 12:34:56',
      'compare' => '>='
    ),
    array(
      'key'     => 'worksheet_name',
      'value'   => 'My Spreadsheet',
      'compare' => '='
    ),
  ),
);

• Tested on WordPress v6.3.1

• This plugin saves Spreadsheet's data to the global area, not to each post. If you want to have Spredsheet data for individual posts, you can link data ID with custom fields.

• The data is added and stored in the wp_google_ss2db table as a JSON-encoded array.

  id	date	worksheet_id	worksheet_name	sheet_name	title	value
  1	2021-08-27 00:00:00	1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms	My Spreadsheet	Sheet1	Data-01	{"area":{"a":["brooklyn","bronx","Queens","Manhattan"],"b":["brooklyn","bronx","Queens","Manhattan"]}}

• This Plugin does not hosting on the wordpress.org repo in order to prevent a flood of support requests from wide audience.

Uncaught Error: Call to undefined method GuzzleHttp\Utils::chooseHandler()

Some of WordPress plugins or Composer packages you are using depend on HTTP client Guzzle v6 (legacy).
You can resolve this error by updating to v7 dependent version.

See CHANGELOG file.

See LICENSE file.

✌️

_{^{A little project by @sectsect}}