wahengchang · June 20, 2025 07:52
diff --git a/google-sheet-to-json-download.gs b/google-sheet-to-json-download.gs
 /**
 * Add both menus on open.
 */
 function onOpen() {
  const ui = SpreadsheetApp.getUi();

  // Export menu
  ui.createMenu('Export')
    .addItem('Download as JSON', 'showDownloadDialog')
    .addToUi();

  // Import menu
  ui.createMenu('Import')
    .addItem('Upload JSON…', 'showUploadDialog')
    .addToUi();
 }


 /**
 * (Re-use from before) – show “Download JSON” dialog.
 */
 function showDownloadDialog() {
  const ss       = SpreadsheetApp.getActive();
  const sheet    = ss.getActiveSheet();
  const sheetName = sheet.getName();
  const values   = sheet.getDataRange().getValues();
  const headers  = values.shift();
  const records  = values.map(r =>
    headers.reduce((o,h,i) => (o[h]=r[i], o), {})
  );
  const json     = JSON.stringify(records, null, 2);
  const html     = `
    <html><body>
      <p>Download JSON for sheet: <strong>${sheetName}</strong></p>
      <a id="dl" style="
        display:inline-block;
        font-size:16px;
        text-decoration:none;
        padding:8px 12px;
        background:#4285f4;
        color:#fff;
        border-radius:4px;
      " href="#">Download JSON</a>
      <script>
        const data = ${JSON.stringify(json)};
        const blob = new Blob([data], {type:"application/json"});
        const url  = URL.createObjectURL(blob);
        const a    = document.getElementById('dl');
        a.href     = url;
        a.download = "${ss.getName()}.json";
      <\/script>
    </body></html>
  `;
  SpreadsheetApp.getUi()
    .showModalDialog(HtmlService.createHtmlOutput(html)
      .setWidth(350)
      .setHeight(180),
      'Export JSON'
    );
 }
 /**
 * Show a file-picker dialog for uploading JSON (inline HTML version).
 */
 function showUploadDialog() {
  const htmlContent = `
    <!DOCTYPE html>
    <html>
      <head>
        <base target="_top">
      </head>
      <body style="font-family: Roboto, sans-serif; margin: 16px;">
        <p>Select a JSON file to import:</p>
        <input type="file" id="fileInput" accept=".json" style="margin-bottom:12px;"/>
        <br/>
        <button 
          onclick="upload()" 
          style="
            font-size:14px; 
            padding:8px 16px; 
            background:#4285f4; 
            color:#fff; 
            border:none; 
            border-radius:4px;
            cursor:pointer;
          ">
          Upload & Import
        </button>
        <script>
          function upload() {
            const fi = document.getElementById('fileInput');
            if (!fi.files.length) {
              alert('Please choose a file.');
              return;
            }
            const reader = new FileReader();
            reader.onload = e => {
              google.script.run
                .withSuccessHandler(() => google.script.host.close())
                .insertJsonData(e.target.result);
            };
            reader.readAsText(fi.files[0]);
          }
        <\/script>
      </body>
    </html>
  `;

  const htmlOutput = HtmlService
    .createHtmlOutput(htmlContent)
    .setWidth(420)
    .setHeight(220);

  SpreadsheetApp.getUi()
    .showModalDialog(htmlOutput, 'Upload JSON');
 }


 /**
 * Parse the uploaded JSON string and dump it as rows+columns
 * into a new sheet named by timestamp.
 */
 function insertJsonData(jsonString) {
  const ss = SpreadsheetApp.getActive();

  // 1) Create a new sheet named with the current timestamp
  const ts = Utilities.formatDate(new Date(),
                 ss.getSpreadsheetTimeZone(),
                 'yyyyMMdd_HHmmss');
  let sheet = ss.getSheetByName(ts);
  if (sheet) {
    sheet.clearContents();
  } else {
    sheet = ss.insertSheet(ts);
  }

  // 2) Parse the JSON
  let data;
  try {
    data = JSON.parse(jsonString);
  } catch (e) {
    SpreadsheetApp.getUi().alert('⚠️ Invalid JSON file.');
    return;
  }
  if (!Array.isArray(data) || data.length === 0) {
    SpreadsheetApp.getUi().alert('⚠️ JSON must be a non-empty array of objects.');
    return;
  }

  // 3) Build headers (union of all keys)
  const headers = [];
  const seen = {};
  data.forEach(obj => {
    Object.keys(obj).forEach(k => {
      if (!seen[k]) {
        seen[k] = true;
        headers.push(k);
      }
    });
  });

  // 4) Build the 2D array of values
  const rows = [ headers ];
  data.forEach(obj => {
    rows.push(headers.map(h => {
      const v = obj[h];
      if (v === null || v === undefined) return '';
      if (typeof v === 'object') return JSON.stringify(v);
      return v;
    }));
  });

  // 5) Write into the new sheet
  sheet.getRange(1, 1, rows.length, headers.length)
       .setValues(rows);
 }
	/**
	* Add both menus on open.
	*/
	function onOpen() {
	const ui = SpreadsheetApp.getUi();

	// Export menu
	ui.createMenu('Export')
	.addItem('Download as JSON', 'showDownloadDialog')
	.addToUi();

	// Import menu
	ui.createMenu('Import')
	.addItem('Upload JSON…', 'showUploadDialog')
	.addToUi();
	}


	/**
	* (Re-use from before) – show “Download JSON” dialog.
	*/
	function showDownloadDialog() {
	const ss = SpreadsheetApp.getActive();
	const sheet = ss.getActiveSheet();
	const sheetName = sheet.getName();
	const values = sheet.getDataRange().getValues();
	const headers = values.shift();
	const records = values.map(r =>
	headers.reduce((o,h,i) => (o[h]=r[i], o), {})
	);
	const json = JSON.stringify(records, null, 2);
	const html = `
	<html><body>
	<p>Download JSON for sheet: <strong>${sheetName}</strong></p>
	<a id="dl" style="
	display:inline-block;
	font-size:16px;
	text-decoration:none;
	padding:8px 12px;
	background:#4285f4;
	color:#fff;
	border-radius:4px;
	" href="#">Download JSON</a>
	<script>
	const data = ${JSON.stringify(json)};
	const blob = new Blob([data], {type:"application/json"});
	const url = URL.createObjectURL(blob);
	const a = document.getElementById('dl');
	a.href = url;
	a.download = "${ss.getName()}.json";
	<\/script>
	</body></html>
	`;
	SpreadsheetApp.getUi()
	.showModalDialog(HtmlService.createHtmlOutput(html)
	.setWidth(350)
	.setHeight(180),
	'Export JSON'
	);
	}
	/**
	* Show a file-picker dialog for uploading JSON (inline HTML version).
	*/
	function showUploadDialog() {
	const htmlContent = `
	<!DOCTYPE html>
	<html>
	<head>
	<base target="_top">
	</head>
	<body style="font-family: Roboto, sans-serif; margin: 16px;">
	<p>Select a JSON file to import:</p>
	<input type="file" id="fileInput" accept=".json" style="margin-bottom:12px;"/>
	<br/>
	<button
	onclick="upload()"
	style="
	font-size:14px;
	padding:8px 16px;
	background:#4285f4;
	color:#fff;
	border:none;
	border-radius:4px;
	cursor:pointer;
	">
	Upload & Import
	</button>
	<script>
	function upload() {
	const fi = document.getElementById('fileInput');
	if (!fi.files.length) {
	alert('Please choose a file.');
	return;
	}
	const reader = new FileReader();
	reader.onload = e => {
	google.script.run
	.withSuccessHandler(() => google.script.host.close())
	.insertJsonData(e.target.result);
	};
	reader.readAsText(fi.files[0]);
	}
	<\/script>
	</body>
	</html>
	`;

	const htmlOutput = HtmlService
	.createHtmlOutput(htmlContent)
	.setWidth(420)
	.setHeight(220);

	SpreadsheetApp.getUi()
	.showModalDialog(htmlOutput, 'Upload JSON');
	}


	/**
	* Parse the uploaded JSON string and dump it as rows+columns
	* into a new sheet named by timestamp.
	*/
	function insertJsonData(jsonString) {
	const ss = SpreadsheetApp.getActive();

	// 1) Create a new sheet named with the current timestamp
	const ts = Utilities.formatDate(new Date(),
	ss.getSpreadsheetTimeZone(),
	'yyyyMMdd_HHmmss');
	let sheet = ss.getSheetByName(ts);
	if (sheet) {
	sheet.clearContents();
	} else {
	sheet = ss.insertSheet(ts);
	}

	// 2) Parse the JSON
	let data;
	try {
	data = JSON.parse(jsonString);
	} catch (e) {
	SpreadsheetApp.getUi().alert('⚠️ Invalid JSON file.');
	return;
	}
	if (!Array.isArray(data) \|\| data.length === 0) {
	SpreadsheetApp.getUi().alert('⚠️ JSON must be a non-empty array of objects.');
	return;
	}

	// 3) Build headers (union of all keys)
	const headers = [];
	const seen = {};
	data.forEach(obj => {
	Object.keys(obj).forEach(k => {
	if (!seen[k]) {
	seen[k] = true;
	headers.push(k);
	}
	});
	});

	// 4) Build the 2D array of values
	const rows = [ headers ];
	data.forEach(obj => {
	rows.push(headers.map(h => {
	const v = obj[h];
	if (v === null \|\| v === undefined) return '';
	if (typeof v === 'object') return JSON.stringify(v);
	return v;
	}));
	});

	// 5) Write into the new sheet
	sheet.getRange(1, 1, rows.length, headers.length)
	.setValues(rows);
	}