title: Google Script - how to replace cell values in Sheet 1 by searching Sheet 2 for those values and returning correlating data from another Sheet 2 column
body: <p>Google Spreadsheet / Scripting Question:</p>
  <p>Disclaimer: I am a beginner. Any explanation helps me tremendously.</p>
  <blockquote>
  <p>Long story short:</p>
  <ol>
  <li>Worksheet 1, Column N contains comma-separated values.</li>
  <li>Worksheet 2, Column A has all possible values that could be combined to make up the content of Worksheet 1, Column N.</li>
  <li>Worksheet 2, Column C contains &quot;alternative&quot; values.</li>
  </ol>
  <p>I need a script that can replace all values in Worksheet 1, Column N with the &quot;alternative&quot; values from Worksheet 2, Column C. I need the final result to be the &quot;alternative&quot; values in a single, comma-separated value.</p>
  <p>I have already scripted to separate Column N values into individual values in new columns. However, I have not been able to advance further. My current splitting script appears at the bottom.</p>
  </blockquote>
  <p>Full explanation:</p>
  <p>&quot;Worksheet 1&quot; contains multiple columns, including Column &quot;N&quot; that contains comma-separated values. The number of rows in Sheet 1 will grow as users enter data. Currently, there are only three rows of data. Column &quot;N&quot; contains:</p>
  <p><br>N1 has a value of &quot;Dog, Cat, Rabbit&quot;
  <br>N2 has a value of &quot;Dog, Fish&quot;
  <br>N3 has a value of &quot;Frog, Horse, Fish&quot;</p>
  <p>My current Google Script gathers data from all available rows, and it then separates the comma-separated values in Column &quot;N&quot; into new columns, like so:</p>
  <p><br>X1 has a value of &quot;Dog&quot;, Y1 has a value of &quot;Cat&quot;, and Z1 has a value &quot;Rabbit&quot;
  <br>X2 has a value of &quot;Dog&quot;, Y2 has a value of &quot;Fish&quot;
  <br>X3 has a value of &quot;Frog&quot;, Y3 has a value of &quot;Horse&quot;, and Z3 has a value of &quot;Fish&quot;</p>
  <p>&quot;Worksheet 2&quot; contains, in column A, all possible values that might appear above -- Dog, Cat, Rabbit, Fish, Horse, etc. So,</p>
  <p><br>A1 has a value of &quot;Dog&quot;
  <br>A2 has a value of &quot;Cat&quot;
  <br>A3 has a value of &quot;Rabbit&quot;
  <br>A4 has a value of &quot;Fish&quot; -- etc. (there are 100+ total possible values).</p>
  <p>&quot;Worksheet 2&quot; contains, in column C, &quot;alternative values&quot; (i.e., corresponding Google Calendar Resource Addresses):</p>
  <p><br>C1 has a value of &quot;domain.com_928313sdf98@resource.calendar.google.com&quot;
  <br>C2 has a value of &quot;domain.com_190382d0931@resource.calendar.google.com&quot;
  <br>C3 has a value of &quot;domain.com_295801a0181@resource.calendar.google.com&quot;
  <br>C4 has a value of &quot;domain.com_5s3910s1481@resource.calendar.google.com&quot; --- etc.</p>
  <p>I am seeking a script that, for the data in Worksheet 1, would search for the separated values in Worksheet 2 and then replace those values in Worksheet 1 with the correlating &quot;alternative values&quot; (i.e., Google Calendar Resource Addresses) from Worksheet 2, Column C. I then need it to concatenante the results back into a comma-separated value.</p>
  <blockquote>
  <p>So, I want to start with Worksheet 1, where N1 has a value of &quot;Dog, Cat, Rabbit,&quot; and I want to get back &quot;domain.com_928313sdf98@resource.calendar.google.com, domain.com_190382d0931@resource.calendar.google.com, domain.com_295801a0181@resource.calendar.google.com&quot; as my final result. <P>The script would perform this across all rows.</p>
  </blockquote>
  <p>My Splitting Script (I am not even sure if this is the best way of handling this):</p>
  <pre><code>function mySplit() {
    var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var startcolumn = 2;
    var startrow = 2;
    var numcolumns = 500;
    var dataRange = sh.getRange(startcolumn, 1, numcolumns, 25);
    var data = dataRange.getValues();
    for (var i = 0; i &lt; data.length; ++i) {
    var cell = sh.getRange(startcolumn + i, 15).getValues()[0];
    var sCell = cell[0].split(&quot;,&quot;);
    sh.getRange(startrow +i,26,1,sCell.length).setValues([sCell]);  
    }}
  </code></pre>