1.58 🔍Build a Charts Dashboard with Google Sheets and HTML Service
Use Apps Script’s HtmlService to build custom dashboards with live charts:
var htmlOutput = HtmlService.createHtmlOutputFromFile('chart');
SpreadsheetApp.getUi().showSidebar(htmlOutput);
Embed charts using Chart.js or Google Charts linked to dynamic sheet data.
1.59 🧪 How to Create Personalized Documents from a Google Spreadsheet in Minutes
Use DocumentApp + Apps Script + a template Doc with placeholders like {{Name}}:
doc.getBody().replaceText("{{Name}}", row[0]);
Generate invoices, letters, or certificates for every row using loops.
1.60 🔍 Fill Google Spreadsheet with Random Data
Use formulas:
=RANDBETWEEN(1,100), =CHAR(RANDBETWEEN(65,90))
Or use Apps Script:
sheet.getRange("A1:A100").setValues([...Array(100)].map(() => [Math.random()]));
1.61 🧪 How to Import CSV Files into Google Spreadsheets with Google Apps Script
Read the uploaded CSV using DriveApp and parse it:
var file = DriveApp.getFilesByName("data.csv").next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
1.62 🔍Convert Excel Files to CSV in Google Drive with Apps Script
Open Excel as Spreadsheet using:
SpreadsheetApp.open(file).getSheets()[0].getDataRange().getValues()
Write to a CSV file using Utilities.formatString() or join rows with commas, then upload back to Drive as a .csv file.
1.63 🧪 How to Email a Range of Google Spreadsheet Cells
Use getRange() and convert values to HTML for rich email format:
var htmlBody = range.getValues().map(r => "<tr>" + r.map(c => "<td>" + c + "</td>").join("") + "</tr>").join("");
Wrap it in a table and send with MailApp.sendEmail().
1.64 🔍 Export Formulas and Notes from a Google Spreadsheet
Use:
range.getFormulas() to get all cell formulas
range.getNotes() to extract user notes
You can export both to a new sheet or external file.
1.65 🧪Send Gravity Forms Data to Google Spreadsheet or Email
Use Gravity Forms’ Webhooks Add-on to send POST data to a Google Web App:
doPost(e) {
const data = JSON.parse(e.postData.contents);
sheet.appendRow([data.name, data.email, data.message]);
}
Deploy the script as a web app and paste the URL in Gravity Forms settings.
1.66 🔍 Get Google Spreadsheets Data as JSON in your Website
Publish your Sheet as a web app or use the public JSON feed:
https://spreadsheets.google.com/feeds/cells/{sheetId}/1/public/full?alt=json
Or use Apps Script to expose a custom API endpoint with doGet().
1.67 🧪Reorder Worksheets in Google Spreadsheets by Name
Use Apps Script to get all sheets and sort them alphabetically:
const sheets = ss.getSheets().sort((a, b) => a.getName().localeCompare(b.getName()));
sheets.forEach((s, i) => ss.setActiveSheet(s).setTabColor(null));
Move them programmatically using insertSheet() + copyTo() for strict reordering.
1.68 🔍 How to Update Multiple Cell Values in Google Sheets with Apps Script
Batch update is more efficient than looping:
const values = [[1, 2], [3, 4]];
sheet.getRange(1, 1, 2, 2).setValues(values);
Always use 2D arrays, even if updating a single row or column.
1.69 🧪 How to Convert Microsoft Excel to Google Spreadsheet Format with Apps Script
Upload an Excel file and convert using:
Drive.Files.copy(fileId, {mimeType: "application/vnd.google-apps.spreadsheet"});
Requires enabling Advanced Drive Service. Automate migration from XLSX to Sheets this way.