How to Import CSV Into Google Sheets

Google documents several ways to import a CSV into Google Sheets. File > Import takes a .csv and offers six placements, from "Create new spreadsheet" to "Append rows to current sheet", with a separator you pick or let Sheets detect. IMPORTDATA pulls a comma-separated file from a URL into a range of cells. Apps Script reads a file out of a Drive folder on a time-driven trigger. Every one of those starts from a seat that already holds edit access to the spreadsheet. Your customer sits outside that seat, holding the box office return their duty manager exported this morning.

The door a web backend has

The Sheets API writes values two ways. values.batchUpdate "Sets values in one or more ranges of a spreadsheet", each range given in A1 notation like Returns!A7:I7. values.append "Appends values to a spreadsheet", and Google explains how it picks its target. "The input range is used to search for existing data and find a 'table' within that range", and the values land on "the next row of the table, starting with the first column of the table". Its insertDataOption chooses between OVERWRITE, where "The new data overwrites existing data in the areas it is written", and INSERT_ROWS, where "Rows are inserted for the new data".

Neither one takes a key. A sheet has no primary key, no unique index and no conflict target, so nothing on Google's side can tell a return that is already in the spreadsheet from one that is new. That decision belongs to whoever calls the API.

One published number shapes the rest. The API allows 60 write requests a minute per user per project, and 300 a minute per project. Google's own example runs against the project ceiling, where an app that sends 350 requests in one minute is told that "the additional 50 requests exceed the quota and generates a 429: Too many requests HTTP status code response". A loop that appends one row per call spends the whole minute on 60 rows.

The return that arrives

An independent cinema sends its week 32 figures, exported from its own ticketing system.

1Screening ID,Venue,Title,Date,Screen Format,Admits,Gross,Comments2SCR-00471,00318,Wolf at the Door,03/04/2026,3-D,142,1240.00,3SCR-00472,00318,Wolf at the Door,03/04/2026,Digital 3D,98,861.50,-2 seats out of service4SCR-00473,318,The Long Field,15/04/2026,2D,64,512.00,5SCR-00474,00204,The Long Field,15/04/2026,35 mm,31,248.00,+1 late show added⋮113 rows not shown119SCR-00588,00204,Harrow Lane,09/04/2026,2D,77,616.00,

The venue calls the column Admits and the distributor calls it Admissions. Dates are written day first. 3-D, Digital 3D and 35 mm are three spellings the trade uses for two of the four formats the distributor tracks. Row 4 lost the leading zeros on its venue code, because that row went through Excel on the way out. Two comments begin with a character that Google's own parser reads as the start of a formula.

The person drags the file into the importer inside your app. Updog Importer reads it in the browser, matches the headers to your schema, holds the format column to your list, and puts every row in front of them. Your onComplete handler receives the rows. The handler posts them to a route you own, and that route decides which rows update a line in the spreadsheet and which ones extend it.

No Updog server stands between the browser and the spreadsheet.

The sheet the rows land in

One sheet holds the season. Column A carries the key.

1Screening ID,Venue code,Film title,Screening date,Format,Admissions,Gross box office,Notes,Updated at

Column A is a key only because your code treats it as one. The spreadsheet enforces nothing, so two rows can carry SCR-00471 and Sheets will hold both.

A spreadsheet takes "Up to 10 million cells or 18,278 columns (column ZZZ)", and Google states that the same restrictions apply to Excel and CSV imports. Nine columns of returns reach that ceiling at a little over a million rows. The spreadsheet owns that ceiling, and it is the number to check before a season of daily returns lands in one file.

The schema in Updog Importer

The columns array describes the sheet as the person sees it.

import type { DataEditorColumn } from "@updog/data-editor";

const FORMATS = ["2D", "3D", "IMAX", "35mm"];

export const columns: DataEditorColumn[] = [
  {
    id: "screeningId",
    title: "Screening ID",
    size: 150,
    validators: [
      { type: "required" },
      { type: "regex", pattern: "^SCR-[0-9]{5}$" },
      { type: "unique" },
    ],
  },
  {
    id: "venueCode",
    title: "Venue code",
    size: 130,
    transformer: (value) => String(value ?? "").trim().padStart(5, "0"),
    validators: [{ type: "required" }, { type: "regex", pattern: "^[0-9]{5}$" }],
  },
  {
    id: "filmTitle",
    title: "Film title",
    size: 220,
    validators: [{ type: "required" }],
  },
  {
    id: "screeningDate",
    title: "Screening date",
    size: 160,
    editor: { type: "date" },
    validators: [{ type: "required" }, { type: "date" }],
  },
  {
    id: "format",
    title: "Format",
    size: 120,
    editor: { type: "select", options: FORMATS, enableCustomValue: false },
    validators: [{ type: "required" }, { type: "oneOf", values: FORMATS }],
  },
  {
    id: "admissions",
    title: "Admissions",
    size: 130,
    editor: { type: "number" },
    validators: [{ type: "number", min: 0, max: 5000, decimalPlaces: 0 }],
  },
  {
    id: "grossBoxOffice",
    title: "Gross box office",
    size: 170,
    editor: { type: "number" },
    formatter: (value) =>
      value
        ? "GBP " + Number(value).toLocaleString("en-GB", {
            minimumFractionDigits: 2,
          })
        : "",
    validators: [{ type: "number", min: 0, max: 200_000, decimalPlaces: 2 }],
  },
  {
    id: "notes",
    title: "Notes",
    size: 260,
  },
];

Each piece earns its place against what arrives. The transformer on the venue code runs as rows enter the store, so 318 and 00318 both become 00318 before anybody looks at the grid. The date editor turns 15/04/2026 into 2026-04-15, and because 15 is above 12 the whole file settles day first, so 03/04/2026 lands as 2026-04-03. The select on the format holds the column to four options, and a value nobody maps is dropped from the row.

{ type: "unique" } on the screening id catches the case the spreadsheet cannot. Uniqueness is relational, so the SDK checks the value against every other row in the column, and the rule always runs last. That covers duplicates inside the file. Rows already sitting in the sheet are the route's job, further down.

Importing a CSV into Airtable covers the other grid-shaped destination, where the option list is enforced by the destination instead of by your schema.

What the person sees and what the sheet gets

grossBoxOffice carries a formatter, which formats the display value without changing stored data. The grid paints GBP 1,240.00 and the store holds 1240.00. The handler receives 1240.00, the route sends the number 1240, and the sheet's own number format paints the currency again.

Every layer here holds one value and shows another. The trouble starts when a layer is left to decide which of the two it keeps, and that is the decision the write call makes for you.

Search reads the formatted value, so a query for GBP finds every filled cell. Find and Replace writes back to the stored value and takes a match only where it sits inside the data, so a search for the GBP the formatter added reports no results.

The headers the venue sends

Column matching scores each header against your column id and your column title, and the higher score wins.

Date sits exactly on the contains floor, which fires once the shorter normalized string runs to four characters. Title and Gross clear the same floor with a character to spare. The built-in synonym table lists the bare title under job title and grosspay under gross salary, and neither group holds a column of yours, so both headers fall through to the contains tier and land on the right column anyway.

Admits and Comments reach nothing on their own. Admits shares no whole word with admissions and runs four characters shorter, past the three edits allowed at ten characters. Comments runs three characters longer than notes, past the two allowed at eight. One synonyms entry each carries them, and every entry you write is a header you never map again.

Value matching runs once across the import. 3-D normalizes to 3d and hits the option exactly, since normalization removes hyphens, spaces, underscores and dots. 35 mm reaches 35mm the same way. Digital 3D lands at seventy, because the contains tier refuses a two-character option and the words digital and 3d give one match out of two.

The mount

The props tie the file, the schema and the sheet together.

<DataEditor<Return>
  apiKey="your-license-key"
  open={open}
  onClose={closeEditor}
  columns={columns}
  primaryKey="screeningId"
  blockSubmitOnError
  synonyms={{
    columns: {
      admissions: ["admits", "attendance", "tickets sold", "seats sold"],
      notes: ["comments", "remarks", "duty manager notes"],
    },
  }}
  onComplete={onComplete}
/>

primaryKey is one column here, and it is the same column the sheet keeps in A. Values are compared after trimming. blockSubmitOnError keeps submit disabled while any row carries an error, so a malformed screening id never reaches your route.

Whatever the person fixes by hand comes back on the result as learnedSynonyms. Store those pairs and feed them back through synonyms, and the next return from the same venue matches itself.

Every snippet here is React. Those props reach Vue, Angular and Svelte through the web component build. The install and the modal wiring beneath this mount live in how to import a CSV file into a React app.

The result on submit

On submit, Updog Importer hands your handler every row grouped by source.

import type { DataEditorResult, ResultRow } from "@updog/data-editor";

const CHUNK_SIZE = 2000;

const toRow = (entry: ResultRow<Return>) => {
  if (entry.isDeleted) return [];
  return [{
    screeningId: entry.row.screeningId,
    venueCode: entry.row.venueCode,
    filmTitle: entry.row.filmTitle,
    screeningDate: entry.row.screeningDate,
    format: entry.row.format,
    admissions: entry.row.admissions,
    grossBoxOffice: entry.row.grossBoxOffice,
    notes: entry.row.notes,
  }];
};

const onComplete = useCallback(async (result: DataEditorResult<Return>) => {
  for (const source of result.sources) {
    const rows = source.rows.flatMap(toRow);

    for (let start = 0; start < rows.length; start += CHUNK_SIZE) {
      const written = await fetch("/api/returns/write", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ rows: rows.slice(start, start + CHUNK_SIZE) }),
      });
      if (!written.ok) throw new Error((await written.json()).message);
    }
  }
}, []);

A deleted row goes nowhere, since removing a line from a spreadsheet shifts every row below it and that is a different job. New and changed rows carry the same payload, because the route works out which is which against the sheet.

The chunk size follows the write budget. Each chunk costs one read request and up to two write requests, so 60 writes a minute carries thirty chunks, and two thousand rows a chunk carries sixty thousand rows a minute. Two thousand is the number chosen against your own endpoint. Thirty chunks is what Google's published quota allows.

Throw when a route answers with a failure. Updog holds submit until your promise resolves, then empties the editor. A handler that traps the error and returns counts as a finished import, and the whole return clears the grid unwritten. A thrown error keeps the grid as it stands, with every mapping and hand correction on it. The person submits again on rows that never left the screen.

The route that writes

The route holds the credentials, and the browser stops there.

import { google } from "googleapis";

const SPREADSHEET_ID = process.env.RETURNS_SPREADSHEET_ID;
const SHEET = "Returns";

const auth = new google.auth.GoogleAuth({
  scopes: ["https://www.googleapis.com/auth/spreadsheets"],
});
const sheets = google.sheets({ version: "v4", auth });

const toValues = (row) => [
  row.screeningId,
  row.venueCode,
  row.filmTitle,
  row.screeningDate,
  row.format,
  Number(row.admissions),
  Number(row.grossBoxOffice),
  row.notes ?? "",
  new Date().toISOString(),
];

const readIndex = async () => {
  const answer = await sheets.spreadsheets.values.get({
    spreadsheetId: SPREADSHEET_ID,
    range: SHEET + "!A2:B",
  });

  const index = new Map();
  (answer.data.values ?? []).forEach((cells, offset) => {
    if (cells[0]) index.set(cells[0], { line: offset + 2, venue: cells[1] });
  });
  return index;
};

A service account is "a special kind of account typically used by an application or compute workload", and Google adds that it "is identified by its email address, which is unique to the account". Google documents how it reaches one spreadsheet. "You can directly share individual files with the service account's email address using the standard UI", treating that address "as a user account in the document's share settings with no administrator privileges required". So the booking team shares the season sheet with the service account and nothing else. Google also warns that "Service account keys are a security risk if not managed correctly", so prefer an environment that hands your runtime a credential over a key file you carry.

readIndex reads columns A and B and maps each screening id to its row number and the venue that owns that line, offset by two because row one is the header. Re-read it for every chunk. A key appended by the previous chunk is in the sheet already, and an index read once would append it twice.

class NotYours extends Error {}

const writeReturns = async (rows, allowed) => {
  const index = await readIndex();
  const updates = [];
  const appends = [];

  for (const row of rows) {
    const found = index.get(row.screeningId);
    if (found) {
      if (!allowed.has(found.venue)) {
        throw new NotYours("A screening id belongs to another venue");
      }
      updates.push({
        range: SHEET + "!A" + found.line + ":I" + found.line,
        values: [toValues(row)],
      });
    } else {
      appends.push(toValues(row));
    }
  }

  if (updates.length > 0) {
    const answer = await sheets.spreadsheets.values.batchUpdate({
      spreadsheetId: SPREADSHEET_ID,
      requestBody: { valueInputOption: "RAW", data: updates },
    });
    if (answer.data.responses?.length !== updates.length) {
      throw new Error("The sheet answered for fewer ranges than were sent");
    }
  }

  if (appends.length > 0) {
    await sheets.spreadsheets.values.append({
      spreadsheetId: SPREADSHEET_ID,
      range: SHEET + "!A:I",
      valueInputOption: "RAW",
      insertDataOption: "INSERT_ROWS",
      requestBody: { values: appends },
    });
  }
};

app.post("/api/returns/write", async (request, response) => {
  const session = await getVerifiedSession(request);
  if (!session) return response.status(401).json({ message: "Not signed in" });

  const rows = request.body.rows;
  if (!Array.isArray(rows)) {
    return response.status(400).json({ message: "Send an array of rows" });
  }

  const allowed = await venuesFor(session.accountId);
  if (rows.some((row) => !allowed.has(row.venueCode))) {
    return response.status(403).json({ message: "A venue code is not yours" });
  }

  try {
    await writeReturns(rows, allowed);
  } catch (error) {
    if (error instanceof NotYours) {
      return response.status(403).json({ message: error.message });
    }
    request.log.error({ err: error });
    return response.status(502).json({ message: "The sheet was not updated" });
  }

  response.json({ written: rows.length });
});

Every row that matched an id becomes one A1 range in a single batchUpdate. Everything else goes to append with INSERT_ROWS, which extends the table rather than writing over whatever sits under it. The response carries "One UpdateValuesResponse per requested range, in the same order as the requests appeared", along with totalUpdatedRows and totalUpdatedCells, so the route counts the answers against what it sent before it reports success.

getVerifiedSession() stands in for your own server-side authentication check. Two fields in the body carry a claim over somebody else's numbers. The venue code is checked against the venues that session may report for. The screening id picks the line the write lands on, so it is checked against the venue already sitting in column B of that line, and a row that would overwrite another cinema's return leaves the sheet untouched with a 403.

Why RAW

Both write calls carry valueInputOption: "RAW", and that word decides what the spreadsheet stores.

Google defines the two options plainly. Under RAW, "The values the user has entered will not be parsed and will be stored as-is". Under USER_ENTERED, "The values will be parsed as if the user typed them into the UI", following "the same rules that are applied when entering text into a cell via the Google Sheets UI". The values guide gives the example twice. Under RAW the input "'=1+2' places the string, not the formula, '=1+2' in the cell". Under USER_ENTERED, "'Mar 1 2016' becomes a date, and '=1+2' becomes a formula".

Two columns in this file depend on which one you send. 00318 is a five-character string that USER_ENTERED would read as the number 318. The comments column is free text a duty manager typed, and USER_ENTERED hands every one of those cells to the parser that runs behind the Sheets UI. The two comments here open with a minus and a plus, and a cell that opens with = becomes a live formula in your spreadsheet, running under your booking team's account.

Updog carries that cell through untouched. Its cell cleaner strips zero-width characters, turns non-breaking spaces into ordinary ones, normalizes to NFC and trims, and it inspects no first character. So a CSV holding =SUM(A1:A9) reaches your handler as that exact string. RAW is what keeps it a string once it lands.

Sending RAW puts the type decision in your route instead, which is why toValues wraps two fields in Number(). The API takes it from there. "For input, supported value types are: bool, string, and double. Null values will be skipped. To set a cell to an empty value, set the string value to an empty string." A JSON number becomes a number cell, a JSON string stays text with its leading zeros intact, and notes falls back to "" so an empty comment writes a blank cell, since a null would be skipped.

The parts nobody ships for you

Updog Importer integrates with nobody. There is no Google Sheets connector, no destination list, no webhook and no server of ours. onComplete hands your code an object, and the route in the middle is work you do. Our cell cleaner leaves a leading = alone by design, so keeping a formula out of your spreadsheet is your valueInputOption and your call.

A Google Sheet is also already a shared spreadsheet. When the venue can simply be given edit access, hand them the link and let File > Import do it, since it offers "Append rows to current sheet" for exactly that. Everything above exists for the case where the season sheet holds forty venues and none of them may see another's numbers, so the file has to arrive through your app, inside a session your app issued. Client-side and server-side CSV import names the jobs each of the two models fits.

The pieces you wrote and next week

You wrote one sheet with nine columns and a key in A, a schema with eight columns, one padding transformer, one synonyms block covering two headers, a route that reads column A and splits the import in two, and two write requests that carry the whole thing. The file stays on the machine that opened it. The rows travel from your own front end to your own route and into your own spreadsheet, and the only party you added to the chain is yourself. Point the same setup at a React CSV importer modal or at the web component and the middle stays the same.

Week 33 brings the same venue and a second one that writes Seats instead of Admits. The mappings from week 32 are already stored, the corrected rows update in place because their screening ids are already in column A, and the new venue costs one more line in synonyms.