This page needs a newer browser to run its tools. You can still read everything here.

Web Scraping & Browser Automation (Python and JavaScript) Module 9

Office automation: Excel, PDF and email interview questions

Interview questions on “Office automation: Excel, PDF and email”: warm-ups, then what each harder question tests and how a good answer starts.

11 questions: 3 warm-ups and 8 with model answers · Levels: Fresher to senior

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

openpyxl or XlsxWriter for a weekly report, and why?

Answered in the lesson Excel automation: openpyxl, XlsxWriter, ExcelJS
Fresher

Why is getting a table out of a PDF harder than getting its text?

Answered in the lesson PDFs: extract text and tables, merge, generate
Fresher

What is the difference between the SMTP envelope and a message's To and From headers?

Answered in the lesson Email reports: build, send and read safely

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Fresher to mid

A weekly job writes a sales workbook with XlsxWriter. People who open it in a spreadsheet app see the right totals, but a dashboard that loads the same file with pandas shows every total as 0. What is happening, how do you confirm it, and what do you change?
What it tests

Knowing that these libraries never calculate formulas, what a formula's cached value is and who reads it, and storing computed results instead of relying on a recalculation.

First step

Open one report as the ZIP file it is and look at a total cell in the sheet's XML. If its value element holds 0 next to the formula, the dashboard reads exactly what the file stores. Then ask how the dashboard reads: anything that does not calculate sees only that stored value.
Follow-ups
- Why do the people who open the file see the right totals?
- Would plain numbers instead of formulas be the better fix?
- How do you stop the next report someone adds from repeating this?
From the lesson Excel automation: openpyxl, XlsxWriter, ExcelJS
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A nightly job builds one workbook of about 300,000 rows by 20 columns with XlsxWriter, and since the data grew it runs out of memory on a machine with 2 GB. How do you change the job, and what do you give up?
What it tests

The memory models of XlsxWriter and openpyxl, their constant_memory and write-only modes and what those modes cannot do, and knowing when a workbook is the wrong file for the data.

First step

Estimate before changing anything: memory per cell in XlsxWriter's normal mode times six million cells. Then list what the workbook needs. If the rows can be written in order and the sheet needs no table and no merged ranges, constant_memory keeps memory flat; if it needs a table, that mode is not available.
Follow-ups
- The finance team insists on an Excel table with a totals row. What now?
- Would openpyxl do better here?
- When is a workbook the wrong file for this data?
From the lesson Excel automation: openpyxl, XlsxWriter, ExcelJS
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A nightly job extracts a supplier's price table from its PDF bulletins. After the supplier changed its template, about a third of the stored prices sit in the wrong columns, and nothing failed. How do you find the cause, repair the data and make the job catch this next time?
What it tests

Seeing that table extraction fails quietly, reading positions and settings instead of guessing, and checks that use the document's own header, counts and totals.

First step

Take one bad night's PDF and look at what the extractor returned for each page, before any cleaning: the number of cells per row and the header row. When they differ from the table the page prints, the reading broke when the template changed, and the stored prices are only as good as that reading.
Follow-ups
- The new template has no ruling lines. How do you find the columns?
- How do you repair the prices already stored?
- Which checks would have stopped the first bad night?
From the lesson PDFs: extract text and tables, merge, generate
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

A job reads the totals of PDF invoices that suppliers email in. For about a fifth of them it finds no text at all, and their totals are stored as empty. What is going on, and how should the job handle those files?
What it tests

Recognising pages without a text layer, routing them instead of guessing, and keeping empty values out of the stored data.

First step

Open a few failing invoices and count, per page, the characters and the images a library such as pdfplumber reports. Pages with images and no characters are scans or photos: there is no text to extract, and the empty totals are the job's bug, not the supplier's.
Follow-ups
- Should the job run OCR on every invoice to be safe?
- How do you stop empty totals from being stored again?
- A supplier's invoices have text but the totals still come out wrong. What next?
From the lesson PDFs: extract text and tables, merge, generate
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

A service extracts text from PDFs that users upload. One upload of under 100 KB made a worker use all its memory and stop, and the queue behind it stalled. How do you make the service safe against such files?
What it tests

Treating documents as untrusted input: limits before and during parsing, isolation in a child process with time and memory limits, and handling a refused file without stopping the queue.

First step

Keep the file and learn what it does before changing code: which step of the parsing used the memory, and how large the data inside the file became. Then list every point where the service trusts an upload: its size, its type, what reading it may cost, and how long it may take.
Follow-ups
- Why not simply raise the worker's memory?
- What does the user who uploaded the file see?
- How do you know the limits are not refusing honest files?
From the lesson PDFs: extract text and tables, merge, generate
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Fresher to mid

A colleague's weekly report script has the mailbox password written in it, and it sends one message with all 60 recipients in the To line. It has been in the team's repository for a year. What risks do you see, and how do you change the script?
What it tests

Treating a committed password as leaked, moving secrets out of code, provider login rules, recipient privacy and testing against a sink before the first real send.

First step

Treat the password as already leaked: it is in the repository's history, and deleting the line does not remove it from there. Change it, or revoke the app password, first. Then list everything else the script exposes: 60 addresses visible to every reader, and a login that may give access to a whole mailbox.
Follow-ups
- Is an app password enough, or should the script use OAuth 2.0?
- How do you send to 60 people without showing their addresses?
- How do you test the changed script without mailing anyone?
From the lesson Email reports: build, send and read safely
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A report job started failing with "certificate verify failed" against the company's SMTP server, and someone fixed it by switching certificate checks off. Why is that dangerous, and what is the right fix?
What it tests

What certificate verification protects, reading the actual TLS error, fixing trust properly, and catching the regression with a test.

First step

Read the full error before changing anything: an unknown authority, an expired certificate and a name that does not match are three different faults with three different owners. Then remember what the check protects here: the login, sent right after the handshake, which anyone between the job and the server could read if the job trusts any certificate.
Follow-ups
- The server uses the company's own certificate authority. How does the job trust it and nothing more?
- How would you test that the job refuses a wrong certificate?
- Would port 465 instead of 587 have avoided the problem?
From the lesson Email reports: build, send and read safely
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

A weekly report goes to 400 people who signed up for it. About 30 messages bounce every week, a few people reply asking to stop, and the provider has begun to delay the job's mail. What do you build?
What it tests

Reading delivery reports and auto-replies safely, keeping the list honest, respecting stop requests, and staying within a provider's limits.

First step

Look at the answers before writing code: read a week's bounces and replies in the mailbox and sort them by kind, the delivery reports with their action and status codes, the auto-replies, the requests to stop. The kinds decide the rules, and the counts say how much the list has decayed.
Follow-ups
- Which bounces remove an address at once, and which only after several weeks?
- How do you make sure the job never answers an auto-reply?
- How do you know the delays are caused by the job and not by the provider?
From the lesson Email reports: build, send and read safely
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes

All Web Scraping & Browser Automation (Python and JavaScript) interview questions · The Web Scraping & Browser Automation (Python and JavaScript) track

Warm-up questions

openpyxl or XlsxWriter for a weekly report, and why?

Why is getting a table out of a PDF harder than getting its text?

What is the difference between the SMTP envelope and a message's To and From headers?

Questions with model answers

A weekly job writes a sales workbook with XlsxWriter. People who open it in a spreadsheet app see the right totals, but a dashboard that loads the same file with pandas shows every total as 0. What is happening, how do you confirm it, and what do you change?

A nightly job builds one workbook of about 300,000 rows by 20 columns with XlsxWriter, and since the data grew it runs out of memory on a machine with 2 GB. How do you change the job, and what do you give up?

A nightly job extracts a supplier's price table from its PDF bulletins. After the supplier changed its template, about a third of the stored prices sit in the wrong columns, and nothing failed. How do you find the cause, repair the data and make the job catch this next time?

A job reads the totals of PDF invoices that suppliers email in. For about a fifth of them it finds no text at all, and their totals are stored as empty. What is going on, and how should the job handle those files?

A service extracts text from PDFs that users upload. One upload of under 100 KB made a worker use all its memory and stop, and the queue behind it stalled. How do you make the service safe against such files?

A colleague's weekly report script has the mailbox password written in it, and it sends one message with all 60 recipients in the To line. It has been in the team's repository for a year. What risks do you see, and how do you change the script?

A report job started failing with "certificate verify failed" against the company's SMTP server, and someone fixed it by switching certificate checks off. Why is that dangerous, and what is the right fix?

A weekly report goes to 400 people who signed up for it. About 30 messages bounce every week, a few people reply asking to stop, and the provider has begun to delay the job's mail. What do you build?