google-apps-script,gmail , Script for Gmail label to Sheets

Script for Gmail label to Sheets


Tag: google-apps-script,gmail

I am trying to make a script that takes an email within a certain label and puts it into google sheets. Im using this function at the moment:

function getMessagesWithLabel() {
 var destArray = new Array();
  var threads = GmailApp.getUserLabelByName('Facebook').getThreads(0,10);

  for(var n in threads){
        var msg = threads[n].getMessages();
        var destArrayRow = new Array();
        destArrayRow.push('thread has '+threads[n].getMessageCount()+' messages');
          for(var m in msg){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
if(ss.getLastRow()==0){sh.getRange(1,1).setValue('getMessagesWithLabel() RESULTS')};

This works at grabbing all the emails in the 'Facebook' label but grabs all the emails every time, creating duplicates every time it runs. I would like for it to just create a new line every time a new one is put into the 'Facebook' label. Any help?


I got the main part of the code working now i need help with extracting the message body, email address, and time stamp into the spreadsheet if possible.


Four thoughts for you to explore.

  1. Use GmailMessage.getDate() to filter out messages you've already handled. Store the time that the script runs in ScriptProperties and reload it as your threshold next time.
  2. Every message has its own unique ID, so recording that along with the body would give you a key to look up. I think this would be slower than the first option, but could be much faster if you stored key values in ScriptProperties.
  3. Use search() to match label and time, again keeping track of when the script has run in ScriptProperties.

    var threads ='label:"Facebook" after:"2015/6/1"');

    That will give threads with any messages added after the given date, but the threads will also have messages prior to that date. You'll probably still have to use one of the earlier options to filter those out. (Unless, as you've indicated in comments, your thread will have no replies and thus only one message.)

  4. Once a thread is processed, remove the label you used to categorize it.

    var oldLabel = GmailApp.getUserLabelByName('Facebook');
    var newLabel = GmailApp.getUserLabelByName('Facebook-processed');
    var threads = oldLabel.getThreads(0,10);
    for(var n in threads){


How can I exclude a sheet in an array, and sort with a range

Good Morning everyone I have a code that was posted here a while back, I recently started using for a google spreadsheet and tweaked it a bit, how ever there are 2 thigs I have not been able to accomplish the first thing is to Exclude the Master sheet from...

Delete or Trash specific file in Drive

I had a script that ran every day at 5 am, that would move a specific file (data.xls) to the trash. However, since DocsList has been retired, the script no longer functions, and I'm having trouble updating it. I've seen a couple of delete/setTrashed scripts posted here, but they all...

Google spreadsheet script authorisation to BigQuery

I have a Google spreadsheet with a script that connects to BigQuery (using this tutorial - It adds an extra menu option and users can run the script that executes a query to BigQuery. It works fine for me and I want to share this spreadsheet with other users...

Access dropdown items in Google App Scripts UiApp

I'm using the older depreciated UiApp class in javascript (because I have to) where I have a dropdown list I need to access the ListBox, and see all the values that it contains. Is there a way to do this? All the items must be present because there is method...

Add self signed certificate using PHPMailer on Gmail account

Is it possible to use self signed or dedicated certificates on a Gmail account with the library PHPMailer? How to do this? And is this a secure option?

Google Calendar: How to check guest is not busy and available to book through google calendar

I am exploring to write google calendar api integration through google scripts or apis. Is there a way I can check a guest is available before I book an event ?...

Google sheet app script errors after laptop sleep

I have a custom function in a sheet that calls a mysql database. It extracts one value from mysql per row, e.g. | A | B -------------------------------- 1 | YES | =readCompleteFromMember(A1) 2 | NO | =readCompleteFromMember(A2) If I let my laptop go to sleep and open it the next...

Google Apps Script: event.setTime error and time format

I'm creating something of a booking system which is nearly complete. Currently I'm collecting data from a form, passing it to a sheet, and then using some of that info to create a calendar event. This all works fine. On creating the event, I'm also collecting the eventID so that...

Output Google Spreadsheet to XML/RSS/Atom using Google AppScript Content Service

So I've searched this site and found nothing really useful so far to accomplish this. I am wanting to turn a spreadsheet into an RSS feed using google script content service. How do I declare column values (i.e. pubDate, Author, Content) so it will output correct RSS/Atom feed. Following their...

Google Apps Script - Sharing outside of domain

So I have followed the tutorial here, and everything works great... for me. google forms file upload complete example What I need to do is add the script to a Google Form, and allow users to upload an image along with the form. I know it will only put it...

php - Gmail doesn't accept emails that contains links

I am using PHPmailer to send emails that contain activation codes to the people who have signed up on my website. Everything worked fine with Yahoo! but not with Gmail. There is no errors in my PHP script. Everything looked okay. I tried to remove all the links from the...

Break Google Apps HTMLService Page into multiple .html files

I want to combine a few related HTMLService interface in to a tabbed page. I can get the tabs working using the code here. But I want to put the page for each Tab into a different html file in the project. How would I show Billets.html inside the div...

How to copy email from Gmail to my server using PHP IMAP?

How can I copy email from Gmail to my server's /home/email directory after connecting to a Gmail mailbox using PHP's IMAP functionality? I want to retrieve every email as a file in MIME format and I want to download the complete MIME file with PHP, not just the body or...

Google Spreadsheet (Using named range in script)

Trying to get my script to use a named range. In that range I am trying to reset the cell colors back to white. I keep getting an object error, can't figure out how to make this work. function resetCheckDirectory() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var tableRange = ss.getRangeByName("rangename"); tableRange.setBackgrounds("#FFF");...

Invoice Receipts in Gmail for Answers In Search

I was creating code at google scripts to test order / invoice reciept markup from Google's own documentation at It did not work. I could be missing something? The structured data testing tool gave me green checks for everything in the script textarea. I'm going to github the...

Pass webapp link parameter to html file in Google Apps Script

I am trying to pass a parameter from a webapp link, so it can be used to pre-fill an html form (using HtmlService). Apps Script - function doGet(e) { var formId = e.parameter.formId; return HtmlService.createTemplateFromFile('Index') .id = formId .evaluate() .setSandboxMode(HtmlService.SandboxMode.IFRAME) } Index.html - <form id="myForm"> <input type="text" name="formId" value="<?=id?>"><br> <input...

Google Sheets Adds-on open automatically when user opens a sheet or clicks a link in worksheet

I created an google sheets add-on. However, it is a bit hidden in the adds-on menu. Is there a way to enable the Adds-on automatically when user opens a sheet or when user clicks a link in a sheet? I searched on Google Sheets documentation but found nothing. EDIT1: Since...

How to prevent Google Forms from converting form input to scientific notation format

I have a simple script set up that sends emails based on Google form entries using a script-based VLookup to get the contact emails. In some cases, Google Forms converts longer numbers entered into the form field to scientific notation. A workaround I have been using is to enter an...

Function to check for first blank cell

so, I'm attempting to define a custom function that returns the row number of the first blank cell it finds within a custom range. function checkForBlank(startRow,startCol) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var checkRow = startRow; var checkCol = startCol; var range = sheet.getRange(checkRow, checkCol); var i...

Running Apps Script from Chrome extension requires authorization

I'm trying to execute a Google Apps Script from an event on my Chrome extension. While it works great, when ran for the first time by a new user, the apps script requires for the user to give it authorization before it can execute. The problem is when running the...

How to remove quote mark from array

I have a script that will import data from a csv file in to a Google sheet. I would like to include the following to remove the quote marks from the data - .replace(/"/g, "") - but am not sure of the best place/syntax to add in to the existing...

PHPMailer not sending e-mail through Gmail, returns blank page

I am trying to set up a contact form at the bottom of my page. I am using PHPMailer and trying to receive emails at my gmail account. Every time I submit the form, my url changes to url/email.php and I get a blank page. What am I doing wrong?...

How to real-time monitor the emails?

Good day! I want to offer the implementation of google apps to my company. Also we have a need to reply on messages, that we recieve through our web-site in 10 minutes time limit. For this purpose our big team of sales managers a handling emails when they have a...

Publish a Spreadsheet Add-on in the Chrome Store

I'm trying to publish a Spreadsheet Add-on following the the app scripts documentation but I get this error: Please fix the following errors: API Console project with the id specified in the manifest's api_console_project_id field, does not have Google Apps Marketplace SDK enabled. api_console_project_id Here you can see a screenshot:...

Script for Gmail label to Sheets

I am trying to make a script that takes an email within a certain label and puts it into google sheets. Im using this function at the moment: function getMessagesWithLabel() { var destArray = new Array(); var threads = GmailApp.getUserLabelByName('Facebook').getThreads(0,10); for(var n in threads){ var msg = threads[n].getMessages(); var destArrayRow...

Search for value in variable Google Apps Script/JS

Trying to determine if calendar events in Google Calendar were created by the user himself or if a CSV bulk upload was performed. If the script finds an ID with "CSV" somewhere in the value, then remove it. I tried to add an if statement where it looks at the...

getJSON from Google Apps Script not working in Safari

I've created this piece of code to handle my user login. var gasUrl = "[my id]/exec"; function submitFormData() { var username = $("#inputEmail").val(); var password = $("#inputPassword").val(); $.getJSON(gasUrl + "?username=" + username + "&password=" + password, function(data) { if(data[0]) { localStorage.setItem("username", data[0].username); localStorage.setItem("id", data[0].id); window.location.href = "/index.php"; } else {...

Get column to display month name Google App Script

How do I set a column to display the name of the month instead of the whole date using the script? The function can manually be performed by selecting Format>Number>August in the menu but I want to do this in code. This is what I've tried but I can't seem...

How to change the subject info of gmail into chinese character?

I got a string =?GB2312?B?zbO8xtGnu/m0ocq10bXP7sS/?= with $overview[0]->subject; in imap_fetch_overview function . How to change the subject info of gmail into chinese character?...

Google Apps Script return array values and use them in a javascript function

I am trying return an array and use it in a javascript function, but it doesn't seem to work. My is as follows: function doGet() { return HtmlService.createHtmlOutputFromFile('test') .setSandboxMode(HtmlService.SandboxMode.IFRAME); } function test() { var locations = []; var ss = SpreadsheetApp.openByUrl(''), sheet = ss.getActiveSheet(), range = ss.getRange("D2:D4"), values =...

Google Scripts + Google API PHP

I am trying to call one of my Google Scripts from my google-api-php-client and I can't find how to do this. I deployed my app as a web app, and I got the web app url which I can call with curl, but I need to call it as my...

DriveApp.getFolderById Permissions

I'm trying to "upgrade" an old script from DocsList to DriveApp and having a problem with a getFolderById call. The script had worked previously using DocsList.getFolder. I have a (previously-created) folder named "Email Archive" that I'm trying to access in script. Through using Logs and Execution Transcript, I've verified that...

Merging Multiple Arrays Evenly/Alternating with Javascript and Google AppScript

I'm trying to merge multiple arrays evenly/alternating in javascript/Google appScript. There are several arrays (5 or 6). I've tried 2 different methods, but neither worked. I don't work a lot with javascript honestly and I've managed to get the code to this point, but I can't get it merge properly;...

Google Spreadsheet setBackgroundColors() “Deprecated”

A gs I have been using in the past to check for duplicates, is no longer working. The script would check all cells in the spread sheet if any were identical it would highlight all their occurrences green. I also had another function that would revert all the cells back...

Gmail API: Insufficient Permission

THE SITUATION: I am testing the Gmail API for my app. I have tested some requests and they are working fine. For example get messages, get user history, get draft list etc.. Basically all the read only requests are working fine. I have instead some issues related with permission with...

Unable to send email using Action Mailer in production

I'm having some trouble sending email using ActionMailer with Gmail. I've attempted to use Mandrill but it's still not sending. When testing ActionMailer with gmail it works in development only but in production there seems to be an issue. I've tried multiple solutions but nothing seems to be working. I've...

Gmail API: Get list of messages labelled with a specific label in php

THE SITUATION I am setting up Gmail API for my app. I need to import all the emails labelled with a specific label, for example TRASH, SPAM, SENT, UNREAD, STARRED etc.. I am able to get the inbox emails list and single message info. And i can get the list...

Google Spreadsheet custom function to add csv in spreadsheet

I am using Google spreadsheets, and in my google spreadsheet i want to import data from my RESTful web service. I cant use =IMPORTDATA due to the fact that i need to add a header to the url for auth purposes. This is the custom function i have so far:...

Javascript (Google Scripts) value from a function is not returning

I think could be a simple one to solve, I am stuck working this simple issue out. I have called a createEvent function to create a google calendar event. As part of this function I also get the google calendar event ID as EventId and want to return it. For...

How to get the last entry containing a certain value in Google Spreadsheet

I have an answer spreadsheet linked to a Google form. I wish to get the last date that and answer was submitted. Lets say I my entries look like this : DATA : A B 1 Date String 2 2015-05-09 Abb 3 2015-05-11 Bcc 4 2015-05-12 Cdd 5 2015-05-20 Bcc...

Advanced Drive Services in Addons

I have a Google Script that uses the Advanced Drive API service and am now looking to convert this script into an Add-on. However the Drive API needs to be enabled manually by the user before using the service inside the Apps Script. Is it possible to use the advanced...

Google Form to Document Merge to Pdf to Mail

I think my issue is in the e.values I am using along with the ('keyXXXXX', XXXXX); that I am using. The original code I used was something I borrowed from TJ Houston's site. I am using it for an application process. I wrote the code following the process of using...

Script to check for email subject not in inbox runs without error but not as expected. Not sure what is wrong with syntax.

I get an automated email when an event occurs, but need to be notified if it isn't received. It's easy to overlook something that doesn't happen. Here's what I have, but the var c remains 0 when script ends (I'm expecting it to not be 0 if email is found,...

Constantly getting an email: Cannot read property “0” from undefined

I am constantly getting emails from "me" with this in it: Cannot read property "0" from undefined. I think it has something to do with Google Apps script because it started when I tried to code something. (for details: I was trying to code a "Contact Us Form" for my...

Output API data to a csv file

I have Frankenstined bits of code together found on here and on various Google tutorials. My goal is to get data from the Google Analytics API and output it to a file in my Google Drive "my_data.csv". I am able to get my data from the GA API in a...

Google Sheets API for clickable images in Google Sheets

In Google sheets, you can insert a clickable image to a cell which links to a function in app script. Can we also insert this clickable image and link it to app script from our server using Google Spreadsheet API? Or Can we call app script from our server?

Generate and send iCal event to outlook

Im using DDay.iCal (, from nuget) to generate a email event. Im using the following code to generate the event: iCalendar iCal = new iCalendar { Method = "PUBLISH", Version = "2.0" }; //REQUEST for update Event calendarEvent = iCal.Create<Event>(); calendarEvent.Summary = "Test"; calendarEvent.Start = new iCalDateTime(new DateTime(2015, 7, 1,...