enniosousa · May 28, 2025 21:56
diff --git a/addon.gs b/addon.gs
 function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Exportar SQL")
    .addItem("Exportar como PostgreSQL", "exportSheetToPostgresSQL")
    .addToUi();
 }

 function slugify(text) {
  return text
    .toString()
    .normalize('NFD')                  // separa acentos das letras
    .replace(/[\u0300-\u036f]/g, '')   // remove os acentos
    .replace(/[^a-zA-Z0-9.]+/g, '_')   // preserva ponto e substitui o resto por underscore
    .replace(/^_+|_+$/g, '')           // remove underscores do começo e fim
    .toLowerCase();
 }

 function exportSheetToPostgresSQL() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const sheetName = sheet.getName();
  const defaultTableName = slugify(sheetName);
  const ui = SpreadsheetApp.getUi();

  // Prompt com input pré-preenchido
  const html = HtmlService.createHtmlOutput(`
    <label>Nome da tabela:</label><br>
    <input type="text" id="table" value="${defaultTableName}" />
    <br><br>
    <button onclick="google.script.run.withSuccessHandler(() => google.script.host.close()).exportWithTableName(document.getElementById('table').value)">OK</button>
    <button onclick="google.script.host.close()">Cancelar</button>
  `).setWidth(300).setHeight(150);
  ui.showModalDialog(html, "Exportar como SQL");
 }

 function exportWithTableName(tableName) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();

  if (data.length < 2) return;

  const tableParts = tableName.split(".");
  const tableSQLName = tableParts.map(part => `"${part}"`).join(".");

  const headers = data[0];
  const rows = data.slice(1).filter(row => row.join("") !== ""); // Ignora linhas vazias
  const columnTypes = inferColumnTypes(rows, headers.length);
  const nullableColumns = getNullableColumns(rows, headers.length);

  let sql = "";
  sql += `-- DROP TABLE IF EXISTS ${tableSQLName};\n\n`;
  sql += `CREATE TABLE ${tableSQLName} (\n`;
  headers.forEach((col, i) => {
    const nullable = nullableColumns[i] ? "" : " NOT NULL";
    const line = `  "${col}" ${columnTypes[i]}${nullable}`;
    sql += line + (i < headers.length - 1 ? "," : "") + "\n";
  });
  sql += `);\n\n`;

  const valuesSQL = rows.map(row => {
    const formatted = row.map((cell, i) => sqlFormat(cell, columnTypes[i]));
    return `(${formatted.join(", ")})`;
  });

  sql += `INSERT INTO ${tableSQLName} (${headers.map(h => `"${h}"`).join(", ")})\nVALUES\n  ${valuesSQL.join(",\n  ")};\n`;

  // Criação da URL com encoding seguro
  const encoded = encodeURIComponent(sql);
  const filename = `${tableName}.sql`;
  const html = `
    <html><body>
      <a href="data:text/sql;charset=utf-8,${encoded}" download="${filename}">
        Clique aqui para baixar o arquivo SQL
      </a>
    </body></html>
  `;
  SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutput(html).setWidth(300).setHeight(120), "Download do SQL");
 }


 function inferColumnTypes(rows, columnCount) {
  const types = Array(columnCount).fill("VARCHAR");
  for (let col = 0; col < columnCount; col++) {
    for (const row of rows) {
      const val = row[col];
      if (val === "" || val === null) continue;
      if (typeof val === "number") {
        types[col] = Number.isInteger(val) ? "INTEGER" : "NUMERIC";
        break;
      }
      if (typeof val === "boolean") {
        types[col] = "BOOLEAN";
        break;
      }
      if (val instanceof Date) {
        types[col] = "DATE";
        break;
      }
    }
  }
  return types;
 }

 function getNullableColumns(rows, columnCount) {
  return Array.from({ length: columnCount }, (_, col) =>
    rows.some(row => row[col] === "" || row[col] === null)
  );
 }

 function sqlFormat(value, type) {
  if (value === "" || value === null) return "NULL";
  if (type === "INTEGER" || type === "NUMERIC") return value;
  if (type === "BOOLEAN") return value ? "TRUE" : "FALSE";
  if (type === "DATE") return `'${Utilities.formatDate(new Date(value), Session.getScriptTimeZone(), "yyyy-MM-dd")}'`;
  return `'${String(value).replace(/'/g, "''")}'`; // VARCHAR
 }
	function onOpen() {
	const ui = SpreadsheetApp.getUi();
	ui.createMenu("Exportar SQL")
	.addItem("Exportar como PostgreSQL", "exportSheetToPostgresSQL")
	.addToUi();
	}

	function slugify(text) {
	return text
	.toString()
	.normalize('NFD') // separa acentos das letras
	.replace(/[\u0300-\u036f]/g, '') // remove os acentos
	.replace(/[^a-zA-Z0-9.]+/g, '_') // preserva ponto e substitui o resto por underscore
	.replace(/^_+\|_+$/g, '') // remove underscores do começo e fim
	.toLowerCase();
	}

	function exportSheetToPostgresSQL() {
	const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
	const sheetName = sheet.getName();
	const defaultTableName = slugify(sheetName);
	const ui = SpreadsheetApp.getUi();

	// Prompt com input pré-preenchido
	const html = HtmlService.createHtmlOutput(`
	<label>Nome da tabela:</label><br>
	<input type="text" id="table" value="${defaultTableName}" />
	<br><br>
	<button onclick="google.script.run.withSuccessHandler(() => google.script.host.close()).exportWithTableName(document.getElementById('table').value)">OK</button>
	<button onclick="google.script.host.close()">Cancelar</button>
	`).setWidth(300).setHeight(150);
	ui.showModalDialog(html, "Exportar como SQL");
	}

	function exportWithTableName(tableName) {
	const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
	const data = sheet.getDataRange().getValues();

	if (data.length < 2) return;

	const tableParts = tableName.split(".");
	const tableSQLName = tableParts.map(part => `"${part}"`).join(".");

	const headers = data[0];
	const rows = data.slice(1).filter(row => row.join("") !== ""); // Ignora linhas vazias
	const columnTypes = inferColumnTypes(rows, headers.length);
	const nullableColumns = getNullableColumns(rows, headers.length);

	let sql = "";
	sql += `-- DROP TABLE IF EXISTS ${tableSQLName};\n\n`;
	sql += `CREATE TABLE ${tableSQLName} (\n`;
	headers.forEach((col, i) => {
	const nullable = nullableColumns[i] ? "" : " NOT NULL";
	const line = ` "${col}" ${columnTypes[i]}${nullable}`;
	sql += line + (i < headers.length - 1 ? "," : "") + "\n";
	});
	sql += `);\n\n`;

	const valuesSQL = rows.map(row => {
	const formatted = row.map((cell, i) => sqlFormat(cell, columnTypes[i]));
	return `(${formatted.join(", ")})`;
	});

	sql += `INSERT INTO ${tableSQLName} (${headers.map(h => `"${h}"`).join(", ")})\nVALUES\n ${valuesSQL.join(",\n ")};\n`;

	// Criação da URL com encoding seguro
	const encoded = encodeURIComponent(sql);
	const filename = `${tableName}.sql`;
	const html = `
	<html><body>
	<a href="data:text/sql;charset=utf-8,${encoded}" download="${filename}">
	Clique aqui para baixar o arquivo SQL
	</a>
	</body></html>
	`;
	SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutput(html).setWidth(300).setHeight(120), "Download do SQL");
	}


	function inferColumnTypes(rows, columnCount) {
	const types = Array(columnCount).fill("VARCHAR");
	for (let col = 0; col < columnCount; col++) {
	for (const row of rows) {
	const val = row[col];
	if (val === "" \|\| val === null) continue;
	if (typeof val === "number") {
	types[col] = Number.isInteger(val) ? "INTEGER" : "NUMERIC";
	break;
	}
	if (typeof val === "boolean") {
	types[col] = "BOOLEAN";
	break;
	}
	if (val instanceof Date) {
	types[col] = "DATE";
	break;
	}
	}
	}
	return types;
	}

	function getNullableColumns(rows, columnCount) {
	return Array.from({ length: columnCount }, (_, col) =>
	rows.some(row => row[col] === "" \|\| row[col] === null)
	);
	}

	function sqlFormat(value, type) {
	if (value === "" \|\| value === null) return "NULL";
	if (type === "INTEGER" \|\| type === "NUMERIC") return value;
	if (type === "BOOLEAN") return value ? "TRUE" : "FALSE";
	if (type === "DATE") return `'${Utilities.formatDate(new Date(value), Session.getScriptTimeZone(), "yyyy-MM-dd")}'`;
	return `'${String(value).replace(/'/g, "''")}'`; // VARCHAR
	}