Download only visible rows and columns as CSV in Google Apps/Sheets

Question

I have a custom sidebar generated via Apps Script on a Google Sheet which has both a View Option "filter" (radio buttons which shows/hides certain columns and rows) together with an Export CSV button.

The intended functionality is for the user to select their desired View Option and click the Export CSV button and a CSV file will be added to their download queue. The resulting CSV file will only include the rows & columns visible on the screen (due to the View Option).

The following code successfully downloads the CSV file, but includes visible and hidden rows/columns:

HTML CSV Export button

<h4>Export as CSV</h4>
    <form id="thisSheetForm">
      <button class="red" onclick="download('csv')">Export as CSV</button>
    </form>

JS Function Handler in sidebar.html

<script>
      function download(type) {
        google.script.run
        .withSuccessHandler(({ data, filename }) => {
          if (data && filename) {
            const a = document.createElement("a");
            document.body.appendChild(a);
            a.download = filename;
            a.href = data;
            a.click();
          }
        })
        .createDataUrl(type);
      }
    </script>

Function in code.gs

function createDataUrl(type) {
  const mimeTypes = { csv: MimeType.CSV, pdf: MimeType.PDF };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  let url = null;
  if (type == "csv") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=csv&gid=${sheet.getSheetId()}`;
  } else if (type == "pdf") {
    url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&gid=${sheet.getSheetId()}`;
  }
  if (url) {
    const blob = UrlFetchApp.fetch(url, {
      headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
    }).getBlob();
    return {
      data:
        `data:${mimeTypes[type]};base64,` +
        Utilities.base64Encode(blob.getBytes()),
      filename: `${sheet.getSheetName()}.${type}`,
    };
  }
  return { data: null, filename: null };
}

The above createDataUrl() function was the only way I had any success downloading the CSV file. All the other options I tried either brought up a javascript error re: security or just downloaded it to Drive instead of adding to the users browser download queue.

But I'm now struggling to see how I can manipulate the function so the resulting CSV data only includes the columns and rows I want. Is there a way to modify the url to only pull off certain columns/rows or is what I'm trying to do simply not possible?

Any help would be hugely appreciated.

Tanaike · Accepted Answer · 2022-05-30T04:07:16.427

I believe your goal is as follows.

Your sheet hides the rows and columns.
You want to export a sheet as CSV data by filtering the rows and columns by clicking a button.

Modification points:

In your Google Apps Script, whole sheet values are exported.
In your HTML and Javascript, I'm worried that when the button is clicked, withSuccessHandler is not run by the redirect.

When these points are reflected in your script, how about the following modification?

Modified script:

HTML & Javascript: `sidebar.html`

<body>
<h4>Export as CSV</h4>
<form id="thisSheetForm">
  <button class="red" onclick="download(); return false;">Export as CSV</button>
</form>
</body>
<script>
function download(type) {
  google.script.run
  .withSuccessHandler(({ data, filename }) => {
    if (data && filename) {
      const a = document.createElement("a");
      document.body.appendChild(a);
      a.download = filename;
      a.href = data;
      a.click();
    }
  })
  .createDataUrl(type);
}
</script>

Google Apps Script: `code.gs`

function createDataUrl(type) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/gviz/tq?tqx=out:csv&gid=${sheet.getSheetId()}`;
  const csv = UrlFetchApp.fetch(url, { headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` } }).getContentText();
  const ar = Utilities.parseCsv(csv);
  const hiddenColumns = ar[0].reduce((col, _, i) => {
    if (!sheet.isColumnHiddenByUser(i + 1)) col.push(i);
    return col;
  }, []);
  const str = ar.map(r => hiddenColumns.map(c => isNaN(r[c]) ? `"${r[c].replace('"', '\\"')}"` : r[c]).join(",")).join("\n"); // Modified
  const blob = Utilities.newBlob(str);
  return { data: `data:text/csv;base64,` + Utilities.base64Encode(blob.getBytes()), filename: `${sheet.getSheetName()}.csv` };
}

When this script is run, the active sheet is exported as CSV data by filtering the hidden rows and columns.

Reference:

isColumnHiddenByUser(columnPosition)

Added:

From I can't seem to get the above code to work on filtering out the rows - either when they're hidden via hide row or via a filter view., I tested this situation again. By this, it was found that when the rows are hidden by both the manual operation and the basic filter, it seems that the CSV data retrieved by the endpoint of https://docs.google.com/spreadsheets/d/${ss.getId()}/gviz/tq?tqx=out:csv&gid=${sheet.getSheetId()} are broke. From this situation, in this case, I would like to propose to use Sheets API instead of the above endpoint. Ref When this is used, please modify the above "Google Apps Script: code.gs" as follows.

Google Apps Script: `code.gs`

Please enable Sheets API at Advanced Google services. In this case, the filtered rows and columns are retrieved by one API call.

function createDataUrl() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const res = Sheets.Spreadsheets.get(ss.getId(), { ranges: [sheet.getSheetName()], fields: "sheets/data" });
  const showRows = res.sheets[0].data[0].rowMetadata.flatMap(({ hiddenByFilter, hiddenByUser }, i) => hiddenByFilter || hiddenByUser ? i : []);
  const showCols = res.sheets[0].data[0].columnMetadata.flatMap(({ hiddenByFilter, hiddenByUser }, i) => hiddenByFilter || hiddenByUser ? i : []);
  const values = sheet.getDataRange().getDisplayValues().filter((_, i) => !showRows.includes(i)).map(r => r.filter((_, j) => !showCols.includes(j)));
  const str = values.map(r => r.map(c => isNaN(c) ? `"${c.replace('"', '\\"')}"` : c).join(",")).join("\n");
  const blob = Utilities.newBlob(str);
  return { data: `data:text/csv;base64,` + Utilities.base64Encode(blob.getBytes()), filename: `${sheet.getSheetName()}.csv` };
}

I appreciate all your work! In this one, maybe you omit `isRowHiddenByFilter and isRowHiddenByUser`, that's why I take the liberty of posting the solution I was working on. — Mike Steelson, May 29 '22 at 07:42
@Mike Steelson Thank you. When values are retrieved by the endpoint of query language, the filtered values can be retrieved. [Ref](https://tanaikech.github.io/2019/07/28/retrieving-values-from-filtered-sheet-in-spreadsheet-using-google-apps-script/) But, in that case, it seems that only filtered rows can be detected. So, I added `isColumnHiddenByUser`. I had thought that this URL can be reflected in both filtered rows and columns. But, from this question, I could know that only filtered rows can be reflected. This is new information for me. If this was also useful for you, I'm glad. — Tanaike, May 29 '22 at 08:02
ok, i understand now that the endpoint take care of filtered rows — Mike Steelson, May 29 '22 at 09:30
so as not to duplicate your answer, not to be too close to it, I changed mine in another way to give an original solution with no endpoint and no specific html file — Mike Steelson, May 29 '22 at 14:01
Thanks so much for helping me on both my questions in the past few days - you've been extremely helpful! I can't seem to get the above code to work on filtering out the rows - either when they're hidden via hide row or via a filter view. I also forgot to add in my question that some of the cells may include commas, so I'm having the issue of the CSV file also splitting at these commas too — JackPDLTD, May 29 '22 at 15:57
Thanks to one of your other answers, I managed to resolve the comma in cell issue by adding: var array = ar.map(function(e) {return e.map(function(f) {return ~f.indexOf(",") ? '"' + f + '"' : f})}); const str = array.map(r => hiddenColumns.map(c => r[c]).join(",")).join("\n"); In place of: const str = ar.map(r => hiddenColumns.map(c => r[c]).join(",")).join("\n"); const blob = Utilities.newBlob(str); — JackPDLTD, May 29 '22 at 16:16
So I'm just left with trying to solve the issue with filtered rows still showing up in the CSV file - will report back if I have any progress — JackPDLTD, May 29 '22 at 16:17
@JackPDLTD Thank you for replying. About `I also forgot to add in my question that some of the cells may include commas, so I'm having the issue of the CSV file also splitting at these commas too`, I modified my sample script. Could you please confirm it? — Tanaike, May 29 '22 at 23:01
@Mike Steelson Thank you for your concern. I have a good impression of your style. — Tanaike, May 30 '22 at 00:16
@JackPDLTD When I tested the sample sheet again, it was found that when the rows are hidden by both the manual operation and the basic filter, it seems that the CSV data retrieved by the endpoint of `https://docs.google.com/spreadsheets/d/${ss.getId()}/gviz/tq?tqx=out:csv&gid=${sheet.getSheetId()}` are broken. For this, I added a modified script. Could you please confirm it? — Tanaike, May 30 '22 at 04:08

Mike Steelson · Answer 2 · 2022-05-30T02:27:40.737

You will need to fetch only visible cells by

references

Try in gs (no specific html file)

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem(' Download file ...', 'downloadCSVOnlyVisible')
    .addToUi();
}
function downloadCSVOnlyVisible() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sh = ss.getActiveSheet()
  let source = []
  const sep = ',';
  sh.getDataRange().getValues().forEach((r, i) => {
    if (!sh.isRowHiddenByFilter(i + 1) && !sh.isRowHiddenByUser(i + 1)) {
      let prov = []
      r.forEach((c, j) => {
        if (!sh.isColumnHiddenByUser(j + 1)) {
          prov.push(isNaN(c) ? (c.includes(sep) ? `"${c.replace('"', '\\"')}"` : c) : c)
        }
      })
      source.push([prov])
    }
  })
  const content = source.map(r => r.join(sep) + '\n').join('');
  const type = 'csv'
  const mimeTypes = { csv: MimeType.CSV };
  const name = ss.getName() + ' ' + sh.getName() + '.csv'
  const id = DriveApp.createFile(name, content).getId();
  const blob = DriveApp.getFileById(id).getBlob();
  const infoHtml = {
    data: `data:${mimeTypes[type]};base64,` + Utilities.base64Encode(blob.getBytes()),
    filename: `${name}`,
  };
  const html = HtmlService.createHtmlOutput(`<a href="${infoHtml.data}" download="${infoHtml.filename}">${infoHtml.filename}</a>`)
    .setWidth(420).setHeight(100);
  SpreadsheetApp.getUi().showModalDialog(html, "Download your file ...")
}

Thanks for this Mike, hugely appreciate the effort. Includes both rows and columns which is great and exactly what I need. I've just realised that some of the content in the sheet does and will include commas so I'm getting the issue with the CSV file splitting based on the commas in cells as well. — JackPDLTD, May 29 '22 at 15:48
Look at this solution https://stackoverflow.com/a/4617967/15019380 — Mike Steelson, May 29 '22 at 18:08
I have modified the script to add quotes around non-numeric values — Mike Steelson, May 30 '22 at 02:17

Download only visible rows and columns as CSV in Google Apps/Sheets

2 Answers2

Modification points:

Modified script:

HTML & Javascript: sidebar.html

Google Apps Script: code.gs

Reference:

Added:

Google Apps Script: code.gs

HTML & Javascript: `sidebar.html`

Google Apps Script: `code.gs`

Google Apps Script: `code.gs`