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

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  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.

CodePudding user response：

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  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 => r[c]).join(",")).join("\n");
  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)

CodePudding user response：

You will need to fetch only visible cells by

  var source = []
  sh.getDataRange().getValues().forEach((r, i) => {
    if (!sh.isRowHiddenByFilter(i   1) && !sh.isRowHiddenByUser(i   1)) {
      var prov = []
      r.forEach((c, j) => {
        if (!sh.isColumnHiddenByUser(j   1)) {
          prov.push(c)
        }
      })
      source.push([prov])
    }
  })

So, try in gs

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('

Modification points:

Modified script:

HTML & Javascript: sidebar.html

Google Apps Script: code.gs

Reference:

HTML & Javascript: `sidebar.html`

Google Apps Script: `code.gs`