0

my company has switched to managing lead generation in Google Sheets, and we're trying to make the in-Sheets process more efficient. Here's an example sheet: https://docs.google.com/spreadsheets/d/1MrJiHDd7MUXp9eWspMR8gqjA_fBIv9w5vfos3oJqDxI/edit?usp=sharing.

The goal is a script that can be run from a menu that does the following:

  1. Copy data from Data
  2. Paste values only in Destination in the row after the last with data
  3. Clear Data dataset (there are some formulas in Data that use a third-party API that I only want to run once)
  4. In Destination, combine rows to remove duplicates, but keeping unique values using "," as a delimiter

Re: 4, here's an example:

amomsvillage.com    A Mom's Village Frank   [email protected]       [email protected]
amomsvillage.com    A Mom's Village Gary    [email protected]       [email protected]

Becomes:

amomsvillage.com    A Mom's Village Frank,Gary  [email protected]       [email protected],[email protected]

Below is the formula I'm using for 1-3, which mostly works (only issue is that it's not getting the actual last row of data, i.e., where A starts having blanks). However, I'm having to separately run PowerTools' Combine Rows to accomplish 4, when it'd be great to do in one step. [Goal] shows how the data should look after 1-4.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Functions')
      .addItem('Copy Paste', 'moveValuesOnly')
      .addToUi();
}
function moveValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange('Data!A2:E');
  var destSheet = ss.getSheetByName("Destination");
  var lastRow = destSheet.getLastRow();
  destSheet.insertRowAfter(lastRow);
  source.copyTo(destSheet.getRange(lastRow + 1,1),{contentsOnly: true});
  source.clear();
}

There are some additional improvements I'd like to make, but I want to get the above first!

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Keith Marshall
  • 1
  • Your attempt for No. 4 is ``rowMerge()`` of the script in the shared spreadsheet? If it's so, can you provide about the issue of the script for achieving No. 4? If it's no, can you provide the latest script of it? – Tanaike Oct 02 '18 at 22:24
  • @Tanaike, I'm actually using the PowerTools add-in as rowMerge() didn't quite work for me (the original script is from here, https://stackoverflow.com/questions/42569657/build-google-app-script-to-combine-similar-rows-into-one-row). The issues are: (1) Does not remove duplicates in Columns B-D. (2) Does not seem to work on Column E. (3) Ideally, would run with prior script, immediately after copy/paste (and not as a separate command). – Keith Marshall Oct 03 '18 at 17:32
  • @Tanaike, any suggestions? – Keith Marshall Oct 08 '18 at 20:29
  • I'm really sorry for my poor skill. I couldn't understand about what you want to do. – Tanaike Oct 08 '18 at 21:47
  • @Tanaike, I'd like a script to accomplish 4 like the example above, and `rowMerge()` isn't working. – Keith Marshall Oct 09 '18 at 17:06
  • @KeithMarshall What progress did you make with your spreadsheet to update lead generation? – Tedinoz Oct 13 '18 at 07:17
  • @Tedinoz, I got rowMerger() working with some work — Columns A-C keep unique values only (line 98), then D-F combine with "," delimiter. I had to add a new final column with X's, as I don't really understand how lines 107-114 work. Otherwise, Column E combines but with no delimiter. – Keith Marshall Oct 14 '18 at 18:50

0 Answers0