link product sku & stock to Google spreadsheet!

@sharif.tanveer or anyone else finding this,

Try to follow this article: Reading and Writing Google Sheets in PHP.

Also, here's is my custom Drush command that only reads from a Google sheet:

namespace Drupal\custom_module\Drush\Commands;

use Drupal\commerce_stock_local\Event\LocalStockTransactionEvent;
use Drupal\commerce_stock_local\Event\LocalStockTransactionEvents;
use Drupal\commerce_stock\StockTransactionsInterface;
use Drupal\Core\Database\Connection;
use Drupal\Core\Entity\EntityTypeManager;
use Drupal\Core\File\FileSystemInterface;
use Drush\Attributes as CLI;
use Drush\Commands\DrushCommands;
use GuzzleHttp\ClientInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;

/**
 * Drush commands for Google.
 */
class GoogleCommands extends DrushCommands {

  /**
   * Name for module's configuration object.
   */
  const SETTINGS = 'custom_module.settings';

  /**
   * The entity type manager.
   *
   * @var \Drupal\Core\Entity\EntityTypeManager
   */
  protected $entityTypeManager;

  /**
   * The Guzzle client for http requests.
   *
   * @var \GuzzleHttp\ClientInterface
   */
  protected $httpClient;

  /**
   * Default database connection.
   *
   * @var \Drupal\Core\Database\Connection
   */
  protected $connection;

  /**
   * The file system service.
   */
  protected FileSystemInterface $fileSystem;

  /**
   * TaxjarCommands constructor.
   *
   * @param \Drupal\Core\Entity\EntityTypeManager $entity_type_manager
   *   The entity_type.manager service.
   * @param \GuzzleHttp\ClientInterface $http_client
   *   The module_handler service.
   * @param \Drupal\Core\Database\Connection $connection
   *   Default database connection.
   * @param \Drupal\Core\File\FileUrlGenerator $file_url_generator
   *   For getting the url of images.
   */
  public function __construct(EntityTypeManager $entity_type_manager, ClientInterface $http_client, Connection $connection, FileSystemInterface $fileSystem) {
    parent::__construct();
    $this->entityTypeManager = $entity_type_manager;
    $this->httpClient = $http_client;
    $this->connection = $connection;
    $this->fileSystem = $fileSystem;
  }

  /**
   * {@inheritdoc}
   */
  public static function create(ContainerInterface $container) {
    return new static(
      $container->get('entity_type.manager'),
      $container->get('http_client'),
      $container->get('database'),
      $container->get('file_system'),
    );
  }

  /**
   * Sync inventory with a specific Google Sheet.
   *
   * @validate-module-enabled commerce_stock_product
   *
   * @command google:syncInventory
   * @aliases google:syncInventory
   */
  #[CLI\Command(name: 'google:syncInventory', aliases: ['google-syncInventory'])]
  #[CLI\Usage(name: 'google:syncInventory google-syncInventory', description: 'Sync inventory for the last 1 day')]
  public function syncInventory() {
    $messages = [];
    // configure the Google Client
    //https://www.nidup.io/blog/manipulate-google-sheets-in-php-with-api
    $client = new \Google_Client();
    $client->setApplicationName('Google Sheets API');
    $client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
    $client->setAccessType('offline');

    $config = \Drupal::config(self::SETTINGS);
    $fid = $config->get('credentials_file')[0];
    $file = $this->entityTypeManager->getStorage('file')->load($fid);
    $credentials_path = $this->fileSystem->realpath('private://' . $file->getFilename());

    $client->setAuthConfig($credentials_path);

    // configure the Sheets Service
    $service = new \Google_Service_Sheets($client);
    $spreadsheetId = 'qwerttyik57684-0easdfsadfasdfsdd3sphUs0bLx8';
    $spreadsheet_url = $config->get('url');
    $path = parse_url($spreadsheet_url)['path'];
    $parts = explode('/', $path);
    foreach ($parts as $index => $part) {
      if ($part == 'd') {
        $spreadsheetId = $parts[$index + 1];
        break;
      }
    }

    // get all the rows of a sheet
    $range = 'Inventory Check!A:J'; // here we use the name of the Sheet to get all the rows
    $response = $service->spreadsheets_values->get($spreadsheetId, $range);
    $values = $response->getValues();
    $stockCheckerService = \Drupal::service('commerce_stock_product.product_local_stock_checker');
    $eventDispatcher = \Drupal::service('event_dispatcher');
    $database = \Drupal::database();
    $config_factory = \Drupal::service('config.factory');
    $stock_config = $config_factory->get('commerce_stock_local.transactions');

    //  Remove the top hidden row
    array_shift($values);
    //  Remove the "Orders counted to:" row
    array_shift($values);

    $headers = reset($values);
    $headers = array_map('strtolower', str_replace(' ', '', $headers));
    foreach ($values as $row) {
      $row = array_pad($row, count($headers), '');
      $row = array_combine($headers, $row);
      if (!array_key_exists('sku', $row)) {
        continue;
      }
      $products = $this->entityTypeManager->getStorage('commerce_product')->loadByProperties([
        'status' => TRUE,
        'field_sku_root' => $row['sku'],
      ]);
      /** @var \Drupal\commerce_product\Entity\ProductInterface $product */
      $product = reset($products);
      if (!$product) {
        continue;
      }
      if ($product->hasField('field_stock_level')) {
        $stock_level = $product->get('field_stock_level')->first();
        ($stock_level)
          ? $available_stock = $stock_level->get('available_stock')->getValue()
          : $available_stock = 0;
      } else
        $available_stock = 0;

      if ((array_key_exists('availableinventory', $row)) && (is_numeric($row['availableinventory']))) {

        $metadata = [];
        $location_id = 1;

        // Get optional fields.
        $related_tid = $metadata['related_tid'] ?? NULL;
        $related_oid = $metadata['related_oid'] ?? NULL;
        $related_uid = $metadata['related_uid'] ?? NULL;
        $data = $metadata['data'] ?? NULL;

        // Create a transaction record.
        $field_values = [
          'entity_id' => $product->id(),
          'entity_type' => $product->getEntityTypeId(),
          'qty' => $row['availableinventory'],
          'location_id' => $location_id,
          'location_zone' => '',
          'unit_cost' => 0,
          'currency_code' => 'USD',
          'transaction_time' => time(),
          'transaction_type_id' => StockTransactionsInterface::STOCK_IN,
          'related_tid' => $related_tid,
          'related_oid' => $related_oid,
          'related_uid' => $related_uid,
          'data' => serialize($data),
        ];
        $event = new LocalStockTransactionEvent($this->entityTypeManager, $field_values);

        //  Since we basically reset the inventory every day, delete all previous transactions
        $database->delete('commerce_stock_transaction')
        ->condition('location_id', $location_id)
          ->condition('entity_id', $product->id())
          ->condition('entity_type', $product->getEntityTypeId())
          ->execute();

        // Create a STOCK_IN transaction
        $eventDispatcher->dispatch($event, LocalStockTransactionEvents::LOCAL_STOCK_TRANSACTION_CREATE);
        $insert = $database->insert('commerce_stock_transaction')
          ->fields(array_keys($field_values))
          ->values(array_values($field_values))->execute();

        $eventDispatcher->dispatch($event, LocalStockTransactionEvents::LOCAL_STOCK_TRANSACTION_INSERT);

        //  Update the field_stock_level attached to the product
        $existing = $database->select('commerce_product__field_stock_level', 'fsl')
          ->fields('fsl')
          ->condition('entity_id', $product->id())
          ->execute()->fetch();
        if ($existing) {
          $database->update('commerce_product__field_stock_level')
            ->fields([
              'field_stock_level_value' => $row['availableinventory'],
            ])
            ->condition('entity_id', $product->id(), '=')
            ->execute();
        } else {
          $database->insert('commerce_product__field_stock_level')
            ->fields([
              'bundle',
              'deleted',
              'entity_id',
              'revision_id',
              'langcode',
              'delta',
              'field_stock_level_value',
            ])
            ->values([
              $product->bundle(),
              0,
              $product->id(),
              $product->id(),
              'en',
              0,
              $row['availableinventory'],
            ])
            ->execute();
        }

        // Find out if we have real-time aggregation turned on.
        $transactions_aggregation_mode = $stock_config->get('transactions_aggregation_mode');
        if ($transactions_aggregation_mode == 'real-time') {
          // Aggregate if we do.
          $locations = $this->entityTypeManager->getStorage('commerce_stock_location')->loadEnabled($product->getDefaultVariation());

          foreach ($locations as $location) {

            //  updateLocationStockLevel()

            $current_level = $stockCheckerService->getLocationStockLevel($location->getId(), $product->getDefaultVariation());
            $last_update = $current_level['last_transaction'];
            $latest_txn = $stockCheckerService->getLocationStockTransactionLatest($location->getId(), $product->getDefaultVariation());
            $latest_sum = $stockCheckerService->getLocationStockTransactionSum($location->getId(), $product->getDefaultVariation(), $last_update, $latest_txn);
            $new_level = $row['availableinventory'];

            // setLocationStockLevel()

            $existing = $database->select('commerce_stock_location_level', 'll')
              ->fields('ll')
              ->condition('location_id', $location->getId())
              ->condition('entity_id', $product->id())
              ->condition('entity_type', $product->getEntityTypeId())
              ->execute()->fetch();
            if ($existing) {
              $database->update('commerce_stock_location_level')
                ->fields([
                  'qty' => $new_level,
                  'last_transaction_id' => $latest_txn,
                ])
                ->condition('location_id', $location->getId(), '=')
                ->condition('entity_id', $product->id(), '=')
                ->condition('entity_type', $product->getEntityTypeId())
                ->execute();
            } else {
              $database->insert('commerce_stock_location_level')
                ->fields([
                  'location_id',
                  'entity_id',
                  'entity_type',
                  'qty',
                  'last_transaction_id',
                ])
                ->values([
                  $location->getId(),
                  $product->id(),
                  $product->getEntityTypeId(),
                  $new_level,
                  $latest_txn,
                ])
                ->execute();
            }

          }
        }

        $product->save();

      }
      $messages[] = $product->getTitle() . ' Inventory Updated. (' . $available_stock . ' to ' . $row['availableinventory'] . ')';
    }
    \Drupal::logger('custom_module')->info(implode("<br/>", $messages));
  }

}