/**
* Google Ads Assessment - Backend (Google Apps Script)
* - Holds the 50 questions AND the answer key (answers never reach the browser before scoring)
* - Scores every submission on the server
* - Writes to "Student Results" and "Individual Answers"
*
* Setup: Project Settings > Script properties > add API_SECRET (long random text)
* Then Deploy > New deployment > Web app (Execute as: Me, Access: Anyone)
*/
const SHEET_ID = '1cTibut-a2xpsBVyXAkEggqigXsAqG5HwuCIhSc_7n8I';
const RESULTS_SHEET = 'Student Results';
const ANSWERS_SHEET = 'Individual Answers';
const TIMEZONE = 'Asia/Kolkata';
const LETTERS = ['A', 'B', 'C', 'D'];
// d = difficulty (E/M/H), a = index of correct option (0=A, 1=B, 2=C, 3=D)
const QUESTIONS = [
// ---------- EASY (15) ----------
{ d: 'E', q: 'In Google Ads, what is an "impression"?',
o: ['Someone clicks on your ad', 'Someone buys from your website', 'Your ad is shown to a person one time', 'Google approves your ad'], a: 2,
e: 'An impression is counted every time your ad is shown. A click is different: it happens only when someone clicks on the ad.' },
{ d: 'E', q: 'An ad received 10,000 impressions and 500 clicks. What is the CTR?',
o: ['15%', '10%', '2%', '5%'], a: 3,
e: 'CTR = Clicks ÷ Impressions × 100. So 500 ÷ 10,000 × 100 = 5%.' },
{ d: 'E', q: 'A campaign got 200 clicks and the total spend was ₹2,000. What is the average CPC?',
o: ['₹10', '₹100', '₹1', '₹20'], a: 0,
e: 'CPC = Total spend ÷ Clicks. So ₹2,000 ÷ 200 = ₹10 per click.' },
{ d: 'E', q: 'A Google Ads campaign received 500 clicks and generated 25 conversions. What is the conversion rate?',
o: ['25%', '5%', '2%', '20%'], a: 1,
e: 'Conversion rate = Conversions ÷ Clicks × 100. So 25 ÷ 500 × 100 = 5%.' },
{ d: 'E', q: 'An ad was shown 1,000 times and its CTR is 4%. How many clicks did it get?',
o: ['4', '400', '40', '14'], a: 2,
e: 'Clicks = Impressions × CTR. So 1,000 × 4% = 40 clicks.' },
{ d: 'E', q: 'Rina searches "plumber near me" on Google. Which campaign type can show a text ad to her on the Google search results page?',
o: ['Search campaign', 'Display campaign', 'Video campaign', 'Demand Gen campaign'], a: 0,
e: 'Search campaigns show text ads on Google search results when people search for something related to your keywords.' },
{ d: 'E', q: 'What is a "keyword" in Google Ads?',
o: ['The name of your campaign', 'The money you spend on ads', 'The website address of your business', 'A word or phrase you add so your ad can show when people search for something similar'], a: 3,
e: 'Keywords are the words or phrases you choose to tell Google which searches your ad should show for.' },
{ d: 'E', q: 'What does CPC stand for?',
o: ['Cost Per Campaign', 'Cost Per Click', 'Clicks Per Customer', 'Cost Per Content'], a: 1,
e: 'CPC means Cost Per Click: the amount you pay when someone clicks on your ad.' },
{ d: 'E', q: 'Which one is the best example of a conversion for a coaching institute?',
o: ['A person sees the ad', 'A person clicks the ad', 'A person fills in the enquiry form on the website after clicking', 'A person scrolls past the ad'], a: 2,
e: 'A conversion is a valuable action you want, such as an enquiry form, call or purchase. Seeing or clicking an ad is not yet a conversion.' },
{ d: 'E', q: 'What does "ad spend" mean?',
o: ['The total money your ads have cost so far', 'The total money your business has earned', 'The number of people who saw your ad', 'The number of ads in your account'], a: 0,
e: 'Ad spend is the total amount of money charged for your ads in a given time.' },
{ d: 'E', q: 'You set a daily budget of ₹500 for a campaign. What does this mean?',
o: ['You must pay ₹500 every day, even if nobody clicks', 'Each click will cost exactly ₹500', 'Google will charge ₹500 one time and then stop', 'On average, you are happy to spend up to ₹500 per day on this campaign'], a: 3,
e: 'The daily budget is the average amount you want to spend each day. You pay only for clicks, not for the full budget.' },
{ d: 'E', q: 'What is an ad group?',
o: ['A group of customers who saw your ad', 'A set of related keywords and ads inside a campaign', 'A list of all your campaigns', 'A group of negative keywords only'], a: 1,
e: 'An ad group keeps related keywords and ads together inside a campaign, so your ads match what people search for.' },
{ d: 'E', q: 'Where do Display campaign ads usually show?',
o: ['Only on the Google search results page', 'Only inside emails', 'On websites and apps that are part of the Google Display Network', 'Only on YouTube search results'], a: 2,
e: 'Display ads (images and banners) show on many websites and apps in the Google Display Network.' },
{ d: 'E', q: 'What is a negative keyword?',
o: ['A word that stops your ad from showing when a search contains that word', 'A word that gives you more clicks', 'A keyword with a bad Quality Score', 'A word that Google does not allow'], a: 0,
e: 'Negative keywords tell Google not to show your ad for certain searches. This saves money on clicks that are not useful.' },
{ d: 'E', q: 'What is a "search term"?',
o: ['A keyword you add to your ad group', 'The headline of your ad', 'The name of your campaign', 'The actual words a person types into Google'], a: 3,
e: 'A search term is exactly what the person typed into Google. A keyword is what you choose. They can be different.' },
// ---------- MEDIUM (20) ----------
{ d: 'M', q: 'A student searches for "best digital marketing course in Hyderabad". An advertiser is targeting the keyword "digital marketing course". Which match type gives the advertiser the MOST controlled matching?',
o: ['Broad match', 'Exact match', 'Phrase match', 'All match types give the same control'], a: 1,
e: 'Exact match gives the most control because the ad shows only for searches with the same meaning as the keyword. Broad match gives the least control.' },
{ d: 'M', q: 'An advertiser uses the keyword "yoga classes" in phrase match. Which search can show the ad?',
o: ['best yoga classes in Vizag', 'yoga mat price', 'meditation music', 'gym membership offer'], a: 0,
e: 'Phrase match shows the ad when the search includes the meaning of the keyword, with extra words before or after. "yoga mat price" has a different meaning.' },
{ d: 'M', q: 'A dental clinic checks its search terms. Which search term should be added as a negative keyword?',
o: ['dental clinic near me', 'tooth pain treatment', 'teeth cleaning cost', 'dentist jobs in Hyderabad'], a: 3,
e: 'People searching for "jobs" want work, not treatment. Add "jobs" as a negative keyword so you do not pay for these clicks.' },
{ d: 'M', q: 'A campaign spent ₹6,000 and the average CPC was ₹12. How many clicks did it get?',
o: ['72', '5,000', '500', '50'], a: 2,
e: 'Clicks = Total spend ÷ CPC. So ₹6,000 ÷ ₹12 = 500 clicks.' },
{ d: 'M', q: 'A campaign spent ₹15,000 and got 30 conversions. What is the cost per conversion?',
o: ['₹50', '₹500', '₹5,000', '₹450'], a: 1,
e: 'Cost per conversion = Total spend ÷ Conversions. So ₹15,000 ÷ 30 = ₹500.' },
{ d: 'M', q: 'A campaign got 800 clicks and its conversion rate was 5%. How many conversions did it get?',
o: ['4', '400', '5', '40'], a: 3,
e: 'Conversions = Clicks × Conversion rate. So 800 × 5% = 40 conversions.' },
{ d: 'M', q: 'A business adds the keyword "laptop repair". A person types "how to repair laptop screen at home" and sees the ad. Which statement is correct?',
o: ['"laptop repair" is the keyword and the sentence typed by the person is the search term', 'The sentence typed by the person is the keyword and "laptop repair" is the search term', 'Both are keywords', 'Both are search terms'], a: 0,
e: 'The keyword is what the advertiser adds. The search term is what the person actually typed.' },
{ d: 'M', q: 'A coaching institute wants more students to fill in the enquiry form on its website. Which campaign objective fits best?',
o: ['Brand awareness and reach', 'Leads', 'App promotion', 'Local store visits'], a: 1,
e: 'The Leads objective is made to get actions like form fills and calls.' },
{ d: 'M', q: 'Ad A got 2,000 impressions and 100 clicks. Ad B got 1,000 impressions and 80 clicks. Which ad has the better CTR?',
o: ['Ad A, with a CTR of 5%', 'Ad A, because it got more impressions', 'Ad B, with a CTR of 8%', 'Both have the same CTR'], a: 2,
e: 'Ad A: 100 ÷ 2,000 = 5%. Ad B: 80 ÷ 1,000 = 8%. Ad B has the better CTR.' },
{ d: 'M', q: 'A campaign has a daily budget of ₹800. About how much will it spend on average in 30 days?',
o: ['₹8,000', '₹2,400', '₹80,000', '₹24,000'], a: 3,
e: '₹800 × 30 days = ₹24,000. Google may spend a little more on some days and less on others, but the average stays near the daily budget.' },
{ d: 'M', q: 'A jewellery shop sells only new gold ornaments. Which negative keyword is the best choice?',
o: ['second hand', 'gold necklace', 'gold earrings', 'buy gold chain'], a: 0,
e: 'People searching "second hand" want used items, which the shop does not sell. The other options are products the shop sells.' },
{ d: 'M', q: 'Which symbols show a keyword in exact match?',
o: ['Quotation marks: "digital marketing course"', 'A minus sign: -digital marketing course', 'Square brackets: [digital marketing course]', 'No symbols at all'], a: 2,
e: 'Exact match uses square brackets [ ]. Phrase match uses quotation marks, a minus sign makes a negative keyword, and no symbol means broad match.' },
{ d: 'M', q: 'Which Google Ads tool helps you find new keyword ideas and see how often people search for them?',
o: ['Ad Preview tool', 'Keyword Planner', 'Search terms report', 'Billing page'], a: 1,
e: 'Keyword Planner is used for keyword research. It shows keyword ideas and search volume.' },
{ d: 'M', q: 'A new juice brand wants to tell its story with a 30-second video to people watching YouTube. Which campaign type fits best?',
o: ['Video campaign', 'Search campaign', 'Shopping campaign', 'Call-only campaign'], a: 0,
e: 'Video campaigns show video ads on YouTube and other video partners.' },
{ d: 'M', q: 'Which campaign type can show images and videos to people on YouTube, Discover and Gmail to build interest before they search?',
o: ['Search campaign', 'Shopping campaign', 'Call-only campaign', 'Demand Gen campaign'], a: 3,
e: 'Demand Gen campaigns use images and videos to create interest on YouTube, Discover and Gmail.' },
{ d: 'M', q: 'An ad gets many impressions but very few clicks (a very low CTR). What does this most likely mean?',
o: ['The daily budget is too high', 'People see the ad but do not find it interesting or useful enough to click', 'The website is loading slowly', 'Conversion tracking is not working'], a: 1,
e: 'CTR depends on how attractive and relevant the ad is to the person searching. A low CTR means the ad or keyword needs improvement.' },
{ d: 'M', q: 'A campaign got 400 clicks but only 2 conversions. What should you check first?',
o: ['Increase the daily budget', 'Add more broad match keywords', 'Check the search terms and the landing page, because clicks are not turning into enquiries', 'Turn off all negative keywords'], a: 2,
e: 'The conversion rate is only 0.5%. Wrong search terms or a weak landing page are the first things to check.' },
{ d: 'M', q: 'Campaign X spent ₹10,000 and got 20 conversions. Campaign Y spent ₹12,000 and got 40 conversions. Which statement is correct?',
o: ['Campaign X is better because it spent less money', 'Campaign X is better because its cost per conversion is ₹500', 'Both campaigns have the same cost per conversion', 'Campaign Y is better because its cost per conversion is ₹300, while X is ₹500'], a: 3,
e: 'X: ₹10,000 ÷ 20 = ₹500. Y: ₹12,000 ÷ 40 = ₹300. A lower cost per conversion is better, so Y is better.' },
{ d: 'M', q: 'An institute teaches "SEO course" and "Google Ads course". What is the best way to set up its ad groups?',
o: ['Make a separate ad group for each course, with ads that match its keywords', 'Put all keywords in one ad group with one common ad', 'Make one ad group only for the word "course"', 'Do not use ad groups at all'], a: 0,
e: 'Separate ad groups let each ad match the keywords closely, which improves relevance and results.' },
{ d: 'M', q: 'The search terms report shows that "free digital marketing course" cost ₹1,200 and gave no enquiries. The institute sells paid courses only. What is the best action?',
o: ['Add it as an exact match keyword', 'Increase the bid for it', 'Add "free" as a negative keyword', 'Stop all campaigns'], a: 2,
e: 'People looking for free courses will not pay. Adding "free" as a negative keyword stops this wasted spend.' },
// ---------- HARD (15) ----------
{ d: 'H', q: 'A campaign got 20,000 impressions with a CTR of 3%. The average CPC was ₹8. How much did the campaign spend?',
o: ['₹480', '₹4,800', '₹48,000', '₹1,600'], a: 1,
e: 'Clicks = 20,000 × 3% = 600. Spend = 600 × ₹8 = ₹4,800.' },
{ d: 'H', q: 'A campaign spent ₹30,000. The average CPC was ₹15 and the conversion rate was 4%. What was the cost per conversion?',
o: ['₹375', '₹600', '₹60', '₹3,750'], a: 0,
e: 'Clicks = ₹30,000 ÷ ₹15 = 2,000. Conversions = 2,000 × 4% = 80. Cost per conversion = ₹30,000 ÷ 80 = ₹375.' },
{ d: 'H', q: 'A business wants 50 conversions. Its conversion rate is 2% and its CPC is ₹20. How much budget does it need?',
o: ['₹1,000', '₹5,000', '₹25,000', '₹50,000'], a: 3,
e: 'Clicks needed = 50 ÷ 2% = 2,500. Budget = 2,500 × ₹20 = ₹50,000.' },
{ d: 'H', q: 'Campaign P: 10,000 impressions, CTR 2%, conversion rate 5%, CPC ₹10. Campaign Q: 5,000 impressions, CTR 4%, conversion rate 5%, CPC ₹10. Which statement is correct?',
o: ['Campaign P gets more clicks because it has more impressions', 'Campaign Q gets more conversions because its CTR is higher', 'Both campaigns get 200 clicks, 10 conversions and spend ₹2,000', 'Campaign P spends ₹4,000'], a: 2,
e: 'P: 10,000 × 2% = 200 clicks. Q: 5,000 × 4% = 200 clicks. Both get 10 conversions (5% of 200) and spend 200 × ₹10 = ₹2,000.' },
{ d: 'H', q: 'A yoga studio uses broad match keywords and sells paid classes only. Its ads show for "free yoga videos" and "yoga videos at home". What is the best fix?',
o: ['Remove all negative keywords', 'Add "free" and "videos" as negative keywords and use tighter match types', 'Increase the daily budget', 'Add more broad match keywords'], a: 1,
e: 'The searches show people want free videos, not paid classes. Negative keywords and tighter match types remove these searches.' },
{ d: 'H', q: 'An ad for a popular keyword has a CTR of 8% but got only 200 impressions in one week. What is the most likely reason?',
o: ['The budget or bids are too low, so the ad is rarely shown', 'The ad text is poor', 'A CTR of 8% is too high', 'The conversion rate is low'], a: 0,
e: 'An 8% CTR means people like the ad. Very few impressions usually means low budget, low bids or very narrow targeting, so the ad is not being shown enough.' },
{ d: 'H', q: 'Ad Group 1: 10,000 impressions, 400 clicks, ₹4,000 spend, 20 conversions. Ad Group 2: 5,000 impressions, 300 clicks, ₹4,500 spend, 30 conversions. Which ad group has the lower cost per conversion, and what is it?',
o: ['Ad Group 1, ₹200', 'Ad Group 1, ₹100', 'Ad Group 2, ₹15', 'Ad Group 2, ₹150'], a: 3,
e: 'Ad Group 1: ₹4,000 ÷ 20 = ₹200. Ad Group 2: ₹4,500 ÷ 30 = ₹150. Ad Group 2 is cheaper per conversion.' },
{ d: 'H', q: 'A campaign spent ₹9,000 at an average CPC of ₹6. Its CTR was 3%. How many impressions did it get?',
o: ['5,000', '50,000', '500,000', '15,000'], a: 1,
e: 'Clicks = ₹9,000 ÷ ₹6 = 1,500. Impressions = Clicks ÷ CTR = 1,500 ÷ 3% = 50,000.' },
{ d: 'H', q: 'A company sells luxury villas costing over ₹1.5 crore. Its search terms show: "villas for sale in Hyderabad", "rent house in Hyderabad", "villa construction cost per sq ft", "luxury villas near ORR". Which words are best added as negative keywords?',
o: ['"villas for sale" and "luxury villas"', '"villas" and "Hyderabad"', '"rent" and "construction cost"', '"luxury" and "ORR"'], a: 2,
e: 'People who want to rent or build a house are not buyers of ready villas. The other words are useful for this business.' },
{ d: 'H', q: 'An ad has a good CTR of 6%, but the conversion rate is only 0.5%. Which change is most likely to improve conversions?',
o: ['Improve the landing page with a clear offer, a short form and fast loading', 'Increase the CPC bid', 'Show the ad to more people', 'Make the headline more attractive to get even more clicks'], a: 0,
e: 'The ad already gets clicks. The problem is after the click, so the landing page is the best place to improve.' },
{ d: 'H', q: 'Campaign A gets leads at ₹400 per conversion. Campaign B gets similar leads at ₹900 per conversion. You have an extra ₹10,000. If costs stay the same, where should you add it and about how many more conversions can you expect?',
o: ['Campaign B, about 11 more conversions', 'Campaign B, about 25 more conversions', 'Campaign A, about 11 more conversions', 'Campaign A, about 25 more conversions'], a: 3,
e: 'Campaign A is cheaper per conversion. ₹10,000 ÷ ₹400 = 25 more conversions. In Campaign B you would get only about 11.' },
{ d: 'H', q: 'A course costs ₹20,000. A business spent ₹40,000 on ads and got 5 sales. Ignoring other costs, how much money is left after paying for the ads?',
o: ['₹40,000', '₹1,00,000', '₹60,000', '₹80,000'], a: 2,
e: 'Sales = 5 × ₹20,000 = ₹1,00,000. After ads: ₹1,00,000 − ₹40,000 = ₹60,000.' },
{ d: 'H', q: 'You have one broad match keyword, "furniture". The search terms report shows many different searches. How should you use this report?',
o: ['Ignore it, because Google handles everything', 'Add good searches as keywords and add irrelevant searches as negative keywords', 'Delete the campaign', 'Only increase the budget'], a: 1,
e: 'The search terms report shows what people really typed. Use it to add good searches as keywords and block bad ones with negative keywords.' },
{ d: 'H', q: 'Which statement about Search and Demand Gen campaigns is correct?',
o: ['Search shows ads to people who are already searching; Demand Gen creates interest in people who may not be searching yet', 'Demand Gen shows ads to people who are already searching; Search creates interest in people who are not searching', 'Both show only text ads on Google search results', 'Demand Gen works only on Google search results'], a: 0,
e: 'Search reaches people with clear intent. Demand Gen uses images and videos to build interest early.' },
{ d: 'H', q: 'A campaign spent ₹12,000. It got 24,000 impressions, 600 clicks and 12 conversions. Which set of numbers is correct?',
o: ['CTR 25%, CPC ₹20, conversion rate 2%', 'CTR 2.5%, CPC ₹2, conversion rate 20%', 'CTR 2.5%, CPC ₹20, conversion rate 20%', 'CTR 2.5%, CPC ₹20, conversion rate 2%'], a: 3,
e: 'CTR = 600 ÷ 24,000 = 2.5%. CPC = ₹12,000 ÷ 600 = ₹20. Conversion rate = 12 ÷ 600 = 2%.' }
];
// ====================== WEB APP ENTRY ======================
function doGet() {
return json_({ ok: true, service: 'Google Ads Assessment API' });
}
function doPost(e) {
try {
const body = JSON.parse((e && e.postData && e.postData.contents) || '{}');
const secret = PropertiesService.getScriptProperties().getProperty('API_SECRET');
if (!secret || body.secret !== secret) return json_({ ok: false, error: 'unauthorized' });
if (body.action === 'questions') return json_(startAction_(body));
if (body.action === 'submit') return json_(submitAction_(body));
return json_({ ok: false, error: 'bad_action' });
} catch (err) {
console.error(err);
return json_({ ok: false, error: 'server_error' });
}
}
function json_(obj) {
return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}
// Run this once from the editor to authorise and create the two sheets
function setup() {
ensureSheets_();
console.log('Sheets ready. Questions in bank: ' + QUESTIONS.length);
}
// ====================== ACTIONS ======================
function startAction_(b) {
const email = String(b.email || '').toLowerCase();
if (!validEmail_(email)) return { ok: false, error: 'invalid_email' };
const sheets = ensureSheets_();
if (findRow_(sheets.results, 3, email) > 0) return { ok: false, error: 'already_submitted' };
// Questions only: no answers, no explanations
return { ok: true, questions: QUESTIONS.map((x, i) => ({ n: i + 1, q: x.q, o: x.o })) };
}
function submitAction_(b) {
const name = String(b.name || '').trim().replace(/\s+/g, ' ');
const email = String(b.email || '').trim().toLowerCase();
const mobile = cleanMobile_(b.mobile);
const sid = String(b.sid || '');
if (!/^[\p{L}][\p{L}\p{M} .'’-]{1,79}$/u.test(name)) return { ok: false, error: 'invalid_name' };
if (!validEmail_(email)) return { ok: false, error: 'invalid_email' };
if (!/^[6-9]\d{9}$/.test(mobile)) return { ok: false, error: 'invalid_mobile' };
if (!/^[0-9a-f-]{36}$/i.test(sid)) return { ok: false, error: 'invalid_session' };
const answers = b.answers;
if (!Array.isArray(answers) || answers.length !== QUESTIONS.length) return { ok: false, error: 'invalid_answers' };
for (let i = 0; i < answers.length; i++) {
const v = answers[i];
if (!(v === null || (Number.isInteger(v) && v >= 0 && v <= 3))) return { ok: false, error: 'invalid_answers' };
}
const result = score_(name, answers);
const lock = LockService.getScriptLock();
lock.waitLock(30000);
try {
const sheets = ensureSheets_();
// Same submission sent again (e.g. after a network failure): return result, do not write twice
if (findRow_(sheets.results, 11, sid) > 0) return { ok: true, duplicate: true, result: result };
// Same email already submitted with a different session
if (findRow_(sheets.results, 3, email) > 0) return { ok: false, error: 'already_submitted' };
const when = Utilities.formatDate(new Date(), TIMEZONE, 'dd-MMM-yyyy HH:mm:ss');
const rows = result.review.map(r => [
name, email, r.n, r.question,
r.selected === null ? 'Not answered' : LETTERS[r.selected] + '. ' + r.options[r.selected],
LETTERS[r.correct] + '. ' + r.options[r.correct],
r.selected === null ? 'Unanswered' : (r.isCorrect ? 'Correct' : 'Incorrect'),
r.isCorrect ? 1 : 0,
sid
]);
// Answers first, summary row last: the summary row marks a submission as complete
const a = sheets.answers;
a.getRange(a.getLastRow() + 1, 1, rows.length, 9).setValues(rows);
const s = sheets.results;
s.getRange(s.getLastRow() + 1, 1, 1, 11).setValues([[
when, name, email, mobile, result.score, result.percentage / 100,
result.correct, result.incorrect, result.unanswered, 'Completed', sid
]]);
SpreadsheetApp.flush();
} finally {
lock.releaseLock();
}
return { ok: true, result: result };
}
// ====================== SCORING ======================
function score_(name, answers) {
let correct = 0, incorrect = 0, unanswered = 0;
const review = QUESTIONS.map((x, i) => {
const sel = answers[i];
const isCorrect = sel === x.a;
if (sel === null) unanswered++; else if (isCorrect) correct++; else incorrect++;
return { n: i + 1, question: x.q, options: x.o, selected: sel, correct: x.a, isCorrect: isCorrect, explanation: x.e };
});
return {
name: name, score: correct, total: QUESTIONS.length,
percentage: Math.round((correct / QUESTIONS.length) * 10000) / 100,
correct: correct, incorrect: incorrect, unanswered: unanswered, review: review
};
}
// ====================== HELPERS ======================
function validEmail_(e) {
return e.length <= 120 && /^[a-z0-9][a-z0-9._%+-]*@[a-z0-9.-]+\.[a-z]{2,}$/i.test(e);
}
function cleanMobile_(m) {
let s = String(m || '').replace(/[\s-]/g, '');
s = s.replace(/^\+?91(?=\d{10}$)/, '').replace(/^0(?=\d{10}$)/, '');
return s;
}
function findRow_(sheet, col, value) {
const last = sheet.getLastRow();
if (last < 2) return -1;
const vals = sheet.getRange(2, col, last - 1, 1).getValues();
const v = String(value).toLowerCase();
for (let i = 0; i < vals.length; i++) {
if (String(vals[i][0]).toLowerCase() === v) return i + 2;
}
return -1;
}
function ensureSheets_() {
const ss = SpreadsheetApp.openById(SHEET_ID);
const results = prepSheet_(ss, RESULTS_SHEET, [
'Submission Date & Time', 'Student Name', 'Email Address', 'Mobile Number', 'Total Score',
'Percentage', 'Correct Answers', 'Incorrect Answers', 'Unanswered Questions', 'Assessment Status', 'Submission ID'
]);
results.getRange('D:D').setNumberFormat('@');
results.getRange('F:F').setNumberFormat('0.##%');
const answers = prepSheet_(ss, ANSWERS_SHEET, [
'Student Name', 'Student Email', 'Question Number', 'Question', "Student's Selected Answer",
'Correct Answer', 'Result', 'Marks Obtained', 'Submission ID'
]);
return { results: results, answers: answers };
}
function prepSheet_(ss, name, headers) {
let sh = ss.getSheetByName(name);
if (!sh) sh = ss.insertSheet(name);
if (sh.getLastRow() === 0) {
sh.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold').setBackground('#e8eaed');
sh.setFrozenRows(1);
}
return sh;
}
30% off