1

This application uses the google sheet to get information, add them to a different sheet, downloads that sheet and sends that sheet as a mail using nodemailer. How can the firing of this script take place from the google sheets interface?

Gaurav Sangle
  • 11
  • 1
  • Google use ***Apps Script*** to create add-on for Google docs/sheets, steps to create add-on https://www.labnol.org/internet/write-google-docs-addon/28446/ – Sameer Jan 01 '20 at 18:53
  • The current application is complex, I am downloading a sheet and then sending it as a mail...can all of this be done in the add-on format? – Gaurav Sangle Jan 01 '20 at 19:32
  • Yes, may be - I found this https://webapps.stackexchange.com/questions/79324/send-email-with-an-attachment-located-in-google-drive – Sameer Jan 02 '20 at 03:38

1 Answers1

0

What you want can be achieved by using Google Apps Script.

Method 1 - trigger the execution of your Node.js script through GAS

The Node.js script you have can be hosted on the cloud or in a private server and afterwards, you can trigger its execution by using the script below:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  
  ui.createMenu('Custom menu')
    .addItem('Run script', 'runRemoteFunction')
    .addToUi();
}

function runRemoteFunction() {
  var params = {
    'method': 'post',
    'headers': {
      'contentType': 'application/json',
      'payload': '{"name":"Keyboard Cat"}'
    }
  };
  var req = UrlFetchApp.fetch('https://YOUR_REGION-YOUR_PROJECT_ID.cloudfunctions.net/FUNCTION_NAME', params);
  
  var res = req.getContentText();
  
  SpreadsheetApp.getUi().alert(res);
}

The script creates a menu with a button which will fire an HTTP request to an endpoint of your choice by using UrlFetchApp.

enter image description here

Method 2 - migrating to Apps Script

The add-ons are made to extend the functionality of the GSuite applications such as Gmail, Google Sheets and Google Docs.

There are two types of add-ons: Gmail add-ons and Editor add-ons.

Since you want to use both the Sheets and the Gmail, you will most likely have to build an editor add-on. The editor add-on extends the Google Sheets by building customized workflow improvements, establish connectivity to third-party systems, and integrate Sheets data with other G Suite applications. It can have its own set of capabilities, restrictions and special considerations, which have to be taken into account when building it.

In order to build the add-on mentioned, you have to follow these steps:

  1. You have to create the Apps Script project by going at this link;

  2. Develop the add-on - the HTML Service can be used to build the interface and since you mentioned you want to be able to send emails, the Gmail Service can be of help to you.

  3. Test the add-on you have developed;

  4. Publish the add-on.

Furthermore, I suggest you take a look at these links since they can guide you in your future development:

Community
  • 1
  • 1
ale13
  • 5,679
  • 3
  • 10
  • 25
  • Thanks a lot for this thoughtful and comprehensive answer. I followed method 1 and was able to achieve what I needed. Could you please help me to understand how to publish this addon locally and share it with just one person instead of publishing it publicly and having to go through Google review process – Gaurav Sangle Jan 07 '20 at 12:53
  • @GauravSangle unfortunately what you want cannot be achieved. If you want to publish the add-on it **must** be public therefore you have to undergo the Google review process, what you can do instead is share the code. Cheers! – ale13 Jan 07 '20 at 13:01
  • @GauravSangle, furthermor since you're new here, please don't forget to mark the answer as accepted the one which helped most in solving your problem :) See also [How does accepting an answer work?](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). Cheers! – ale13 Jan 07 '20 at 15:52