Laravel: Update records in the DB from a Google Sheets file

laravel_update_records_in_production_from_google_sheets.md

Introducing

If you've been told to update a list of records with the new values in the DB, and it's hard to do it manually, you can run a script within Tinker.

A Google Sheet table structure

The structure might look something like this:

Get a URL on Google Sheets table

1. Go to File
2. Click Share and then select Publish to Web
3. In the Link tab, select the desired Sheet, and also select Comma-separated values (.csv)
4. Copy the following link below

Adjust scripts for your needs

Update via QueryBuilder

If you need to update records via QueryBuilder then you probably need this one:

(function(): void {
    $url = 'https://docs.google.com/spreadsheets/d/e/SomeHash/pub?gid=XXX&single=true&output=csv';
    $fp = fopen($url, 'r');
    if ($fp === false) {
        dump("Error happened on opening CSV file");
        return;
    }

    $header = true;
    while (($row = fgetcsv($fp)) !== false) {
        if ($header) {
            $header = false;
            continue;
        }

        // here you split a row into variables
        // you can also skip unnecessary columns by leaving them empty as following:
        [$id,, $field1,, $field2] = $row;

        SomeModel::where('id', $id)->update([
            'field_1' => $field1,
            'field_2' => $field2,
        ]);
        dump(sprintf("%s [%d] updated", class_basename(SomeModel::class), $id));
    }

    fclose($fp);
})();

Update via model

However, if you need update records via Models with its events then:

1. you have to find the record first
2. update the found model later

As you've mentioned, there're 2 queries per row.

Here's a slight adjustment to the previous script:

$someModel = SomeModel::find($id);
if (is_null($someModel)) {
    dump(sprintf("There is no [%s] with ID [%d]", class_basename(SomeModel::class), $id));
    continue;
}
$someModel->update([
    'field_1' => $field1,
    'field_2' => $field2,
]);

⚠️ Test locally before running in the production

It is highly recommended to test such scripts locally, in order to be confident everything works as expected.

Run the script

In order to run the script on the production server, you need to get there and in the terminal enter a Tinker:

php artisan tinker

Then copy and paste your script in the terminal, and hit Enter.

---

Hooray 🎉 You'll see the progress when records are being updated!