Exporting MySQL Table Structure to Google Sheets

This guide explains how to export MySQL database structure to Google Sheets efficiently. It covers methods for extracting column details, importing them into Sheets, and automating organization using Google Apps Script (GAS).

---

1. Retrieve Column Information from MySQL

Use the INFORMATION_SCHEMA.COLUMNS table to get details about all tables in your database.

SQL Query

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Output Example

TABLE_NAME	COLUMN_NAME	DATA_TYPE	IS_NULLABLE	COLUMN_DEFAULT	COLUMN_TYPE	COLUMN_KEY
users	id	int	NO	NULL	int(11)	PRI
users	name	varchar	YES	NULL	varchar(255)
orders	id	int	NO	NULL	int(11)	PRI
orders	user_id	int	YES	NULL	int(11)	MUL

---

2. Export Column Information as a CSV File

Command to Export MySQL Data to CSV

mysql -u root -p -e "
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, ORDINAL_POSITION;
" > columns_list.csv

Import CSV into Google Sheets

1. Open Google Sheets
2. Go to [File] → [Import]
3. Upload columns_list.csv
4. Select “Replace current sheet”

---

3. Split Data into Separate Sheets per Table

Once the column data is imported, use the following Google Apps Script to create separate sheets for each table.

Google Apps Script (GAS)

function splitTablesIntoSheets() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sourceSheet = ss.getActiveSheet();
    var data = sourceSheet.getDataRange().getValues();

    var headers = data[0];
    var tableColumnIndex = headers.indexOf("TABLE_NAME");
    if (tableColumnIndex === -1) return;

    var tables = {};
    for (var i = 1; i < data.length; i++) {
        var row = data[i];
        var tableName = row[tableColumnIndex];
        if (!tables[tableName]) tables[tableName] = [headers];
        tables[tableName].push(row);
    }

    for (var table in tables) {
        var sheet = ss.getSheetByName(table) || ss.insertSheet(table);
        sheet.clear();
        sheet.getRange(1, 1, tables[table].length, headers.length).setValues(tables[table]);
    }
}

---

4. Format Each Sheet

To ensure uniform formatting, apply the following script:

function adjustSheetHeaders() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();

    sheets.forEach(function(sheet) {
        var sheetName = sheet.getName();
        sheet.getRange("A1").setValue("TABLE_NAME");
        sheet.getRange("B1").setValue(sheetName);
        sheet.deleteRow(2);
    });
}

---

5. Create an INDEX Sheet with All Table Names

This script generates an “INDEX” sheet listing all table names.

function createIndexSheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var indexSheet = ss.getSheetByName("INDEX") || ss.insertSheet("INDEX");
    indexSheet.clear();

    var sheets = ss.getSheets();
    var sheetNames = sheets.map(sheet => [sheet.getName()]).filter(name => name[0] !== "INDEX");

    indexSheet.getRange(1, 1).setValue("Sheet Name");
    if (sheetNames.length > 0) {
        indexSheet.getRange(2, 1, sheetNames.length, 1).setValues(sheetNames);
    }
}

---

6. Automate with Scheduled Execution

To keep the table structure updated, schedule automatic execution using Google Apps Script triggers.

Setting Up a Trigger

1. Open Apps Script
2. Click on the [Clock Icon] to open Triggers
3. Add a new trigger for splitTablesIntoSheets
4. Choose “Time-driven” execution (e.g., daily)

---

Conclusion

Extract MySQL table structure and export it to Google Sheets. Automatically split data into separate sheets per table. Format each sheet with a standard layout. Generate an INDEX sheet listing all tables. Automate the process with scheduled execution.

By following these steps, you can efficiently document and manage MySQL database structures using Google Sheets.