title: Google Apps Script to Generate and Populate Sheet Based on Other Sheet's Data
body: <p>I maintain a spreadsheet workbook that tracks cake orders. Column headers (A–H) on the tracking sheet (named CakeOrders) are as follows: </p>
  
  <p><strong>(A1)</strong> Pickup Date, <strong>(B1)</strong> Client Name, <strong>(C1)</strong> Phone#, <strong>(D1)</strong> Text on Cake, <strong>(E1)</strong> Cake Flavor, <strong>(F1)</strong> Frosting Flavor, <strong>(G1)</strong> Fruit Filling, <strong>(H1)</strong> Link to PO</p>
  
  <p>Each row of data below these headers corresponds to a new purchase order (PO) with a unique date, client name, etc. <a href="https://i.stack.imgur.com/w3eS7.png" rel="nofollow noreferrer">View CakeOrders sheet here.</a></p>
  
  <p>On a second sheet (named Admin) within the same workbook, I have a table that runs match functions to find the Column# for each column header on CakeOrders. This was set up so that my script, which calls upon these Columns#s, will stay accurate even if I end up rearranging or adding Columns and new headers to the CakeOrders sheet—the script will always reference the appropriate column because of the match functions on the Admin sheet. <a href="https://i.stack.imgur.com/3HskE.png" rel="nofollow noreferrer">View the Admin sheet here.</a></p>
  
  <p>I currently have a script set up that allows me to generate new formatted spreadsheets (designed to be blank POs) directly from this spreadsheet. The script names the new sheet (or PO) based on data from within the selected row, and it adds a link to the new PO in Column H.   </p>
  
  <p>I would like to extend this script so that it also fills in select cells on the new PO based on information within its originating row.</p>
  
  <p>Does anyone know a script that would allow me to populate specific cells of a new spreadsheet with data from a specific row of another?</p>
  
  <p>Thanks!</p>
  
  <p>Below is my current script, which creates a new spreadsheet with a name based on row content, stores the new sheet in a specific folder, and includes a link to the spreadsheet in the originating row.</p>
  
  <pre><code>function onOpen() { 
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var customMenu = [{name: "Create PO", functionName: "Create"}]; 
     ss.addMenu("Create PO", customMenu); 
  };
  
  function Create() {
    var sheet = SpreadsheetApp.getActive().getSheetByName("Admin"); 
    var folderID = sheet.getRange("B2").getValue(); //These are column numbers
    var templateID = sheet.getRange("B3").getValue(); 
    var pickupDate = sheet.getRange("B4").getValue(); 
    var clientName = sheet.getRange("B5").getValue();
    var textOnCake = sheet.getRange("B6").getValue(); 
    var cakeFlavor = sheet.getRange("B7").getValue(); 
    var frostingFlavor = sheet.getRange("B8").getValue(); 
    var fruitFilling = sheet.getRange("B9").getValue(); 
    var POColumn = sheet.getRange("B10").getValue(); 
  
    var ss = SpreadsheetApp.getActiveSheet(); // Back to CakeOrders sheet
    var cell = ss.getActiveCell(); 
    var row = cell.getRow(); //User has to pick the correct row
  
    if (row &lt;= 1){ 
     Browser.msgBox("Cannot create new document within the header. Please select the row to which you'd like to add a new PO doc and run the script again.");
      return; 
    }
  
    try{
  
    var pickupDateValue = Utilities.formatDate(ss.getRange(row,pickupDate).getValue(), "America/Chicago", "MMddYY"); 
    } 
    catch(e) { 
    var pickupDateValue = ss.getRange(row,pickupDate).getValue();    
    }
  
    var clientNameValue = ss.getRange(row,clientName).getValue();//getting the actual values now using column numbers from admin sheet
    var textOnCakeValue = ss.getRange(row,textOnCake).getValue(); 
    var cakeFlavorValue = ss.getRange(row,cakeFlavor).getValue(); 
    var frostingFlavorValue = ss.getRange(row,frostingFlavor).getValue(); 
    var fruitFillingValue = ss.getRange(row,fruitFilling).getValue(); 
    var POColumnValue = ss.getRange(row,POColumn).getValue(); 
  
      if (POColumnValue != ""){ 
     Browser.msgBox("There is already content in the PO column. Please clear the contents and try again.");
      return; 
    }
  
  
    if (clientNameValue == "" || textOnCakeValue == "" || cakeFlavor == "" || frostingFlavor == ""|| fruitFilling == ""){ 
     Browser.msgBox("Client Name, Text On Cake, Cake Flavor, Frosting Flavor, and Fruit Filling are required. Please enter a value in each of these columns and then try again.");
      return; 
    }
  
    var docName = "PO." + pickupDateValue + "." + clientNameValue;   
  
    var template = DriveApp.getFileById(templateID);  
    var destFolder = DriveApp.getFolderById(folderID); 
  
    var newDocID = template.makeCopy(docName, destFolder).getId(); 
    var docLink = "https://docs.google.com/spreadsheets/d/" + newDocID; 
    ss.getRange(row,POColumn).setValue(docLink);
  }
  </code></pre>