1.34 🔍 How to Add Options in Google Forms Questions from Google Sheets
Use Google Apps Script to dynamically populate multiple choice or dropdown questions in a Google Form from a list in your Sheet. Example:
let form = FormApp.openById(formId);
let item = form.getItems(FormApp.ItemType.MULTIPLE_CHOICE)[0].asMultipleChoiceItem();
item.setChoiceValues(sheet.getRange("A2:A10").getValues().flat());
1.35 🧪 How to Perform IP Address Lookup with Google Sheets
Use free IP lookup APIs like ipinfo.io with Apps Script:
const response = UrlFetchApp.fetch("https://ipinfo.io/" + ip + "/json");
const data = JSON.parse(response.getContentText());
You can extract city, country, ISP, etc., and populate columns with the info.
1.36 🔍 How to Copy a Formula Down an Entire Column in Google Sheets
Select the cell with your formula and double-click the small blue square in the corner (fill handle). Or, use:
sheet.getRange("B2:B" + sheet.getLastRow()).setFormula("=A2*2");
This auto-fills the formula down column B to match data in column A.
1.37 🧪 How to Use Formulas with Google Form Responses in Sheets
Use an adjacent column to apply formulas to form submissions. Lock formulas using ARRAYFORMULA():
=ARRAYFORMULA(IF(A2:A="", "", A2:A * 2))
This auto-updates as new form responses come in.
1.38 🔍 How to Highlight Duplicates in Google Sheets and Remove
Use conditional formatting:
=COUNTIF(A:A, A1) > 1
to highlight duplicates. To remove them, use:
Data > Data Cleanup > Remove duplicates or filter them out using UNIQUE().
1.39 🧪 Measure Core Web Vitals of your Websites with Google Sheets
Use Chrome UX Report API or PageSpeed Insights API with Apps Script to fetch Lighthouse data:
const response = UrlFetchApp.fetch("https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" + site);
const data = JSON.parse(response.getContentText());
Log metrics like FCP, LCP, CLS, etc., into rows.
1.40 🔍 How to Use Google Sheets with D3.js and Google Visualization
Use Google Sheets as a live data source with D3 or Charts API. Publish the sheet to the web and fetch it with:
https://spreadsheets.google.com/feeds/cells/{sheetId}/1/public/full?alt=json
Then use D3.js to bind and visualize the data in interactive dashboards.
1.41 🧪 How to Get Hidden and Filtered Rows in Google Sheets with Google Script
Use getRowIndex() and isRowHiddenByFilter() or isRowHiddenByUser():
sheet.getRange(i, 1).isRowHiddenByFilter()
Loop through rows to detect which ones are not visible in the active filter view.
1.42 🔍 Convert Numbers to Words using Indian Numbering in Google Sheets
Create a custom function in Apps Script:
function toIndianWords(n) {
// Add code for crore, lakh, thousand, etc.
}
Map numbers like 1050000 to “Ten Lakh Fifty Thousand” in Indian style.
1.43 🧪 How to Import MailChimp Subscribers to Google Sheets
Use MailChimp’s API with Apps Script to pull list members:
const response = UrlFetchApp.fetch("https://
Include API key in headers. Parse and populate your sheet with names, emails, and status.
1.44 🔍 How to Scrape Reddit with Google Scripts
Reddit's public API can be accessed without OAuth for basic data:
UrlFetchApp.fetch("https://www.reddit.com/r/{subreddit}/new.json")
Use JSON.parse() to extract post titles, upvotes, and links into rows.
1.45 🧪 How to Color Alternate Rows in Google Sheets
Use Format > Alternating Colors for visual UI control.
Or use Apps Script:
if (row % 2 == 0) range.setBackground("#f0f0f0");
Apply conditional coloring to give zebra-striping effect.