How to share only certain rows or one tab of a Google Sheet

1. What Google Sheets can do by itself

• Sharing is per file. Share gives a person access to the whole spreadsheet, as a viewer, commenter or editor. There is no permission for one tab, one column or one row.
• Hidden tabs. Hide sheet takes a tab out of sight, but anyone with edit access can unhide it in two clicks, and a downloaded or copied file still has it. It keeps a file tidy; it does not keep a secret.
• Protected sheets and ranges. Data → Protect sheets and ranges stops people from editing. Everyone who can open the file can still see the cells. Google’s own help warns not to use protection as a security measure.
• Filter views. A filter view changes what one person sees on their screen. Everyone else can open the full data, or switch the filter off.
• Publish to the web. File → Share → Publish to web can publish a single tab, but to anyone who has the link, not to one person, and never as “only their rows”.

So the only way to show someone part of a sheet is to give them a different file that holds only that part. The rest of this guide is about making those files and keeping them current.

2. The formula way: IMPORTRANGE and a filter

1. Create a new spreadsheet for one person, such as “Payroll — Ann”.
2. In cell A1, pull in their rows from your main spreadsheet. With the person’s email in column C:

   =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/…", "Payroll!A:F"),
     "select * where Col3 = 'ann@example.com'", 1)

3. Click Allow access when Google asks to connect the two files.
4. Share this file with Ann as a viewer. Repeat for every person.

The rows update by themselves when the main sheet changes. The catch is in Google’s help for IMPORTRANGE: once access is allowed, any editor of the new file can import any part of the main spreadsheet. Share these files view-only, never as editor, and keep the formula out of other people’s hands. Each new person is one more file and one more formula to set up by hand, and leaving out a column means listing the columns you keep in the query.

3. The Apps Script way: one file per person

A script can do the copying for you. This one reads the Payroll tab, groups the rows by the address in the Email column, and makes one spreadsheet per person with the header and their rows, shared with them as a viewer.

1. In your spreadsheet, choose Extensions → Apps Script.
2. Replace the code with the script below. Change the tab name and the column name, and click Save.
3. Click Run and allow the permissions Google asks for.

function shareRowsByEmail() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Payroll');
  const values = sheet.getDataRange().getValues();
  const header = values[0];
  const col = header.indexOf('Email');               // the column that says whose row it is
  const byEmail = {};
  values.slice(1).forEach(function (row) {
    const email = String(row[col]).trim().toLowerCase();
    if (email) (byEmail[email] = byEmail[email] || []).push(row);
  });
  Object.keys(byEmail).forEach(function (email) {
    const rows = [header].concat(byEmail[email]);
    const copy = SpreadsheetApp.create('Payroll — ' + email);
    copy.getSheets()[0].getRange(1, 1, rows.length, header.length).setValues(rows);
    copy.addViewer(email);
  });
}

Because the script copies values with getValues(), formulas arrive as their results and nothing in the new file points back at your spreadsheet. A time-driven trigger (Triggers → Add Trigger → Time-driven) can run it every day.

4. Where the simple script falls short

• New files every run. As written, each run makes a fresh set of spreadsheets. Updating the same file instead means remembering which file belongs to which person, and finding it again if someone deletes it.
• Nobody loses access. When a person leaves the sheet, their old file is still shared with them, with the rows they had. Taking access away is a second job: compare who should have access with who has it.
• Formats are lost. setValues() writes plain values. Currency, percentages and column widths need getNumberFormats(), setNumberFormats() and more code.
• Every column goes out. Leaving out pay, notes or cost prices means picking columns in code, and changing the code whenever the sheet changes.
• Text that turns into something else. A cell such as 00123 or =total written back with setValues() may come out as a number or a formula.
• Download and copy stay on. Turning off download, print and copy for viewers is a Drive setting of each file, not something addViewer() does.
• Six minutes. Google stops a script run after six minutes. With many people, the script must stop in time and continue where it left off.
• Errors go quietly. When a timed run fails, Google emails a failure summary to the person who made the trigger. The people waiting for their rows do not know theirs are stale.

None of this is hard on its own. Together, it is a small program to keep up for every sheet you share this way.

5. With an add-on: ShareLane

ShareLane is our Google Sheets™ add-on for this job. It does what the script above leaves to you:

• By rows: each person gets their own spreadsheet with the header and only the rows that carry their email address. With Pro, a column of names works too, matched to emails in a small table.
• Whole tab: one shared copy of a tab for a list of people.
• Preview shows who gets how many rows before anything is shared.
• Update now rewrites only the copies whose rows changed, shares with new people and removes access from people who are no longer in the sheet. A copy deleted from your Drive is made again.
• Values arrive with your number formats and column widths; formulas arrive as their results; text such as 00123 stays text.
• View-only access, with download, print and copy turned off by default. With Pro, choose the columns people see, and give comment access.
• The copies live in one folder of your own Google Drive™. ShareLane uses Google’s drive.file permission, which reaches only the files it created, not the rest of your Drive.
• One share for up to 3 people is free, updated by hand. Pro, for $4.99 a month or $39 a year, adds automatic updates every hour or every day, up to 20 shares and 100 people per share, column choice and a log tab.

How to set it up: ShareLane help.

ShareLane — Share Rows of a SheetEach person gets only their rows, kept up to date.

Read the setup guide