CODEWITHSUNDEEP
google-apps-script
doebtown
doebtown

Reputation: 563

How to create a comma separated string in Google Apps Script

I need LOTS of help on this one, but I think it's a procedure that would be helpful to other green-programers in the future.

I have a Google Form for people to fill that are new to our neighborhood. It, obviously, feeds a Google Docs spreadsheet. (Here's a link to a copy of the sheet that's just edited to black-out some of the personal data:

https://docs.google.com/spreadsheet/ccc?key=0AjsoIob8dJfodG9WN0ZmWUE1ek9rc3JrVFpDQ0J0OGc

)

One of the questions is which of our groups are you interested in joining. The Form asks the question, then allows the user to check off which of about 10 groups the new member is interested in joining. The data is then fed into the sheet listing all of the groups the new member wants to join in a single cell (that's column F).

The administrative task that I now need to tackle is going through all of the responses and pulling out the members who have said--for example--they're interested in joining the "Helping Hands" group, copying their e-mail address (which is in column X) and pasting it into the Google Groups sign-up sheet for that group.

This is understandable tedious. Especially because--again--we have about 15 of these Google Groups lists that need to be populated.

What would be great would be if I could write a little Google Apps Script that will go through the data in the sheet and give me a comma separated list of all the e-mail addresses that meet the correct criteria so that I could just copy-and-paste that into the Google Group sign up page.

In plain English, the regular expression should say this: - IF column F contains Helping Hands - AND IF column G does NOT contain Yes - THEN add the contents of column X to an exported string, followed by a comma - Repeat for every row in the sheet.

So here's the questions: 1) How do I do this? 2) How does this get triggered?

For the first step, I've already determined that the following regular expression will give me what I need:

/(^|, )Helping Hands($|, )/

I'm not sure how to augment that, though, to include the second required IF statement. I also don't know how to write the THEN statement. And--as I said above--I have NO idea how to trigger all of this.

Thanks in advance to anybody who can help! And the Newcomers to Sewickley also thank you! Please let me know if I can clear anything up.

Upvotes: 0

Views: 5040

Answers (4)

Peter Noges
Peter Noges

Reputation: 68

Evan Plaice provides a nice Google Apps Script that you can use in your own spreadsheet which essentially renders a menu that allows you to transpose a column using a "Text to Column" feature. https://webapps.stackexchange.com/questions/22799/text-to-columns-conversion-in-google-spreadsheets

Upvotes: 0

doebtown
doebtown

Reputation: 563

You have no idea how unbelievable stoked I am that this finally worked out. Thanks a million times over to @Sirik for sticking with it and writing the code and helping me debug it. It does EXACTLY what I want it to and it's just the coolest thing in the world to make it do EXACTLY what I wanted it to.

For the sake of posterity or anybody else who might want to be doing something similar to what I was doing, here's the exact code, all smoothed out, as implemented. You'll see that I added just a few extra bits at the end to e-mail the log to me and give me a dialogue box acknowledging that the script had run (I don't like that feeling of clicking the button and not SEEING something happen).

function Export_Groups_List() {

var options = ['1 & 2 Year Olds\' Playgroup','3 & 4 Year Olds\' Playgroup','Babies\' Playgroup','Book Club','Couples Night Out','Directory','Girls Night Out','Helping Hands','Membership','Newsletter','Serving Sewickley','Treasurer'];
var GROUPS_COL = 5; //This is column 5
var COLG = 6; //Column G
var EMAIL_COL = 23; //Column X
var emailList = ['', '', '', '', '', '', '', '', '', '', '', ''] //12 blanks

    var data = SpreadsheetApp.openById('___').getSheetByName('Master').getDataRange().getValues();
  for (var i = 1; i < data.length ; i++){
    for( var j = 0 ; j < options.length ; j++){
      if (data[i][GROUPS_COL].indexOf(options[j]) != -1 &&
         data[i][COLG] != 'Yes') {
        if (emailList[j] != ''){
          emailList[j] += ', ' ;
        }
        emailList[j] += data[i][EMAIL_COL];
      }
    }
  }
  for ( var i = 0 ; i < 12; i ++){
    Logger.log(options[i] + ': ' + emailList[i])
  }
  MailApp.sendEmail("[email protected]", "Groups Export", Logger.getLog() + " \n\n" + 
                    "At your request, you've been added to this Sewickley Newcomers & Neighbors Google Group." + "\n\n" +
                    "Using Google Groups allows us to always have up to date e-mail lists, make sure that people don't get bombarded with messages they don't want, and facilitate smooth communication between our members. If you have any questions about how to use the Google Groups service with your Newcomers & Neighbors membership, please send an e-mail to [email protected]." + "\n\n" + 
                    "Thank you and thanks for your participation!") ;

  Browser.msgBox("OK. Check your e-mail for the export list. doebtown rocks the house!")
}

Upvotes: 0

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17772

Why don't you go further and set up a on form submit trigger that reads the groups and adds them automatically using the (yet experimental) Groups Services for Apps Script?

Such script is not very difficult to implement, maybe you should check the user guides and tutorials.

Upvotes: 1

Srik
Srik

Reputation: 7967

Looks like you are happy copy-pasting the list of email addresses if you get it in a comma separated format. So you can trigger it yourself when you need it. You can use this code snippet 

var options = ['Helping Hands','Book Club',...]; // Add all 10 options here 
var GROUPS_COL = 5 ; //Column F
var COLG = 6; 
var EMAIL_COL = 23 ; //Column X
var emailList = ['', '','',''...] ; // 10 blanks

var data = SpreadsheetApp.openById('ID_HERE').getSheetByName('SheetName').getvalues(); 
for (var i = 1; i < data.length ; i++){
  for( var j = 0 ; j < options.length ; j++){
    if (data[i][GROUPS_COL].indexOf(options[j]) != -1 &&
        data[i][COLG] != 'Yes') {
      // The entry in row i+1 has checked option[j]
      if (emailList[j] != ''){
        emailList[j] += ',' ; // Add your comma
      }
      emailList[j] += data[i][EMAIL_COL]; 
    }
  }
}

/* Now you have your comma separated list in emailList */
for ( var i = 0 ; i < 10; i ++){
  Logger.log(option[i] + ':' + emailList[i])
}

Obviously, you have to tweak this code to suit your needs, but will give you a fair idea of how to proceed. The code has not been tested.

Upvotes: 0

Related Questions