Search code examples
buttongoogle-sheetsexportemail-attachmentsgoogle-app-maker

How to export a table as google sheet in Google app maker using a button

I've looked extensively and tried to modify multiple sample sets of codes found on different posts in Stack Overflow as well as template documents in Google App Maker, but cannot for the life of me get an export and en email function to work.

UserRecords table:

UserRecords table

This is the area where the data is collected and reviewed, the populated table:

Populated table

These are the data fields I am working with:

Data Fields

This is what the exported Sheet looks like when I go through the motions and do an export through the Deployment tab:

Export Sheet

Lastly, this is the email page that I've built based on tutorials and examples I've seen:

Email Page

What I've learned so far (based on the circles I'm going round in):

  1. Emails seem mostly straight forward, but I don't need to send a message, just an attachment with a subject, similar to using the code:

    function sendEmail_(to, subject, body) {
 var emailObj = {
 to: to,
 subject: subject,
 htmlBody: body,
 noReply: true
 };

MailApp.sendEmail(emailObj);
}

Not sure how to change the "body" to the exported document

  1. To straight up export and view the Sheet from a button click, the closest I've found to a solution is in Document Sample but the references in the code speak to components on the page only. I'm not sure how to modify this to use the table, and also what to change to get it as a sheet instead of a doc.

This may seem trivial to some but I'm a beginner and am struggling to wrap my head around what I'm doing wrong. I've been looking at this for nearly a week. Any help will be greatly appreciated.

Solution

  • In it's simplest form you can do a Google sheet export with the following server script (this is based on a model called employees):

    function exportEmployeeTable() {

  //if only certain roles or individuals can perform this action include proper validation here

  var query = app.models.Employees.newQuery();
  var results = query.run();

  var fields = app.metadata.models.Employees.fields;

  var data = [];

  var header = [];

  for (var i in fields) {
    header.push(fields[i].displayName);
  }

  data.push(header);

  for (var j in results) {
    var rows = [];
    for (var k in fields) {
      rows.push(results[j][fields[k].name]);
    }
    data.push(rows);
  }

  if (data.length > 1) {
    var ss = SpreadsheetApp.create('Employee Export');
    var sheet = ss.getActiveSheet();
    sheet.getRange(1,1,data.length,header.length).setValues(data);

    //here you could return the URL for your spreadsheet back to your client by setting up a successhandler and failure handler
    return ss.getUrl();
  } else {
    throw new app.ManagedError('No Data to export!');
  }
}