How to export spreadsheet header and last row value to csv file using Google apps script

Question

I'm new here and try to seek some expertise to help to create a google apps script.

I have a spreadsheet and want to export the header and the new added row value to csv file and save it into my local c drive and send an alert email with column B value as a subject.

eg. spreadsheets has 14 columns and I would like to export start from column 2 to csv with values like: "column2 value; column3 value; column4 value; column5 value; column6 value; .....column14 value "

Below is my description workflow :

So everytime when people filled up the value in the google forms and press submit, i will received a new row value in a google sheet. Then i will export the header and the latest row value to csv file into my local c drive and send an alert email with column B value as a subject.

Much appreciated if some expertise can help me on this. Thank you so much. :)

Excel sample.xlsx

Answer 1

See if this helps you:

We'll assume you have the spreadsheet configured as follows:

• You've a sheet for submission called Responses
• You've a helper sheet needed for the script called Temp which has the same headers in the first row than the Responses sheet

In the illustrated example below, you want to save as CSV file the headers (Orange) along with the last row submitted (Green)

When you access the script you'll change the following:

• The Spreadsheet ID of the whole document
• The ID of the tab Temp (numbers found after edit#gid=)
• The email address of the recipient

Code:

// Example: https://docs.google.com/spreadsheets/d/1kXaTto1ktqX0XNKQ035897FGX5tV75o7wuYHiNCOqEFfI/edit#gid=1269457815
  // In this link the ID of the Spreadsheet is everything after /d/
  // which is: 1kXaTto1ktqX0XNKQ035897FGX5tV75o7wuYHiNCOqEFfI
  // THE ID of the sheet Temp would be something like: 1269457815

// ---------- Menu ----------
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('CSV File')
  .addItem('Step 1: Email CSV', 'EmailRange')
  .addItem('Step 2: Save CSV file', 'showurl')
  .addToUi();
}



// Choose how do want to export the: csv,pdf,xlsx

EXPORT_TYPE="csv";

function EmailRange() {



  // Enter Sheet ID in between ""

  var sheetId = "ID GOES HERE";
  var ss = SpreadsheetApp.openById(sheetId);
  var sheet=ss.getSheetByName("Responses");

  // You can set up the headers beforehand
  var temp = ss.getSheetByName("Temp");


  //Copy range onto Temp sheet

  var rangeToCopy = sheet.getRange("A"+(ss.getLastRow())+":N"+(ss.getLastRow()));

  // It will erase any previous data

  rangeToCopy.copyTo(temp.getRange(2, 1));


  // Temporarily hide the sheet

  ss.getSheetByName('Responses').hideSheet()


  //Authentification 
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

  var url="https://docs.google.com/spreadsheets/d/"+ss.getId()+"/export?format="+EXPORT_TYPE;

  //Fetch URL of active spreadsheet
  var fetch=UrlFetchApp.fetch(url,params);

  //Get content as blob
  var blob=fetch.getBlob(); 

  var mimetype;
  if(EXPORT_TYPE=="pdf"){
    mimetype="application/pdf";      
  }else if(EXPORT_TYPE=="csv"){
    mimetype="text/csv";    
  }else if(EXPORT_TYPE=="xlsx"){
    mimetype="application/xlsx";   
  }else{
    return;
  }

  // OP: send an alert email with column B value as a subject



    var subject = sheet.getRange("B"+(ss.getLastRow()));
  var timestamp = sheet.getRange("A"+(ss.getLastRow()));
  var Title = subject.getValues();
  var signature = timestamp.getValues();


  //Change Email Recipient underneath

  GmailApp.sendEmail('[email protected]',
                     'Job ID: '+Title, 
                     'Hi there,' + '\n\n' +  'A new entry has been submitted, please find the details in the attached CSV file.'  + '\n\n' +  'Submitted on: '+signature,
                     {
                         attachments: [{
                         fileName: Title + "."+EXPORT_TYPE,
                         content: blob.getBytes(),
                         mimeType: mimetype
                      }]
    });

//Reshow Response sheet

  ss.getSheetByName('Responses').showSheet()

}

function showurl() {

  // Enter Spreadsheet ID after d/ and the TAB ID of Temp after gid=


  var htmlOutput = HtmlService
  .createHtmlOutput('Click here <a href="https://docs.google.com/spreadsheets/d/ID GOES HERE/export?format=csv&gid=TAB ID GOES HERE">My File</a> to download')
  .setWidth(250) //optional
  .setHeight(50); //optional
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Download CSV File');

}

STEP 1: Sending an email to the defined recipient in the script with the subject being the value of the last row in column B, and the signature the submission date & time, like this:

The CSV file will be attached to the email:

STEP 2: From the CSV File Menu which is created when opening the spreadsheet:

Save file to your Local desktop: