Google Apps Script to Generate and Populate Sheet Based on Other Sheet's Data

Question

I maintain a spreadsheet workbook that tracks cake orders. Column headers (A–H) on the tracking sheet (named CakeOrders) are as follows:

(A1) Pickup Date, (B1) Client Name, (C1) Phone#, (D1) Text on Cake, (E1) Cake Flavor, (F1) Frosting Flavor, (G1) Fruit Filling, (H1) Link to PO

Each row of data below these headers corresponds to a new purchase order (PO) with a unique date, client name, etc. View CakeOrders sheet here.

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. View the Admin sheet here.

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.

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.

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?

Thanks!

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.

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 <= 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);
}

Answer 1

Thanks to Cooper for your help. Based on my situation, the best solution I found was to write a second script bound to the PO template (and its duplicates).

The first script, bound to the CakeOrders database, continues to function as it did originally: when triggered, it creates a copy of a PO template, names it based on origin row values, stores it in a designated folder, and stores a URL to the PO in the database.

The script I added to the PO template (below) allows the user to populate the body of the based on origin row values.

function onOpen() { 
var spreadsheet = SpreadsheetApp.getActive(); 
var menuItems = [ {name: 'Fill from Cake Order Form', functionName: 'FillInOrder'}]; 
spreadsheet.addMenu('Fill In Order', menuItems); 
    } 
//Creates drop down

function FillInOrder() { 
var orderSheet = SpreadsheetApp.openByUrl( 'https://docs.google.com/spreadsheets/d/1jX0bT2suuA-4nrYyXNrA5s9gtqxV7hmcgxeb5zUxOeQ/edit#gid=0'); 
var cakeOrders = orderSheet.getSheetByName('CakeOrders') 
var orderRow = Browser.inputBox('Fill from CakeOrders', 'Please enter the row number of the order to use' + ' (for example, "2"):', Browser.Buttons.OK_CANCEL); 
if (orderRow == 'cancel') { return; } var rowNumber = Number(orderRow); if (isNaN(rowNumber) || rowNumber < 2 || rowNumber > cakeOrders.getLastRow()) { 
Browser.msgBox('Error', Utilities.formatString('Row "%s" is not valid.', orderRow), Browser.Buttons.OK); 
return; 
    } 
//Prompts user for row
var pickupDate = cakeOrders.getRange(rowNumber,1).getValue(); 
var clientName = cakeOrders.getRange(rowNumber,2).getValue(); 
var textOnCake = cakeOrders.getRange(rowNumber,3).getValue(); 

var clientSheet = SpreadsheetApp.getActive().getSheetByName("Client"); 

//Gets values from source database

clientSheet.getRange("B8").setValue(pickupDate); 
clientSheet.getRange("B4").setValue(clientName); 
clientSheet.getRange("B12").setValue(textOnCake); 
}
//Sets values in new PO

Answer 2

First of let me say that I'm not suggesting that this is the only or even the best way to proceed. It's just the way I would do it. And I've been working as a lone programmer for most of my life so I almost always do it my way.

I would probably modify the column headings by making them single word Camel Code and use them as the keys for an associative array. So that when I get data from a row I build an array from a table that look like this:

PickupDate  ClientName    Phone         CakeText             CakeFlavor     FrostingFlavor  FruitFilling    LinktoPO
4/15/2017   Bart Simpson  123-456-7899  Your Wierd Bart      Dessert Cactus Frozen Spinach  Tomato  
4/16/2017   Bugs Bunny    987-654-3210  Happy Birthdays Bugs Carrot         Carrot          Carrots 

To an array that looks like this:

var rowA = {PickupData:'4/16/2017',ClientName:'Bugs Bunny',Phone:'987-654-3210',CakeText:'Happy Birthday Bugs',CakeFlavor:'Carrot',FrostingFlavor:'Carrot', FruitFilling:'Carrots',LinkToPO:''}

Now when you go in to your PO form to fill in data you will be able to enter the data like this:

rowA[PickupData] 
rowA[ClientName] etc...

The keys come from the column headers and the values come from which ever row your own. So it doesn't really matter what the order of the columns is. And later you can change the names of the variable by changing the names of the columns.

You can store the other information in a file as a string with file.setContent('string') and then take it out with file.getBlob().getDataAsString().split(delimiter) and split it with any delimiter that you choose. I often choose really wierd ones that you'll never expect to see anywhere like '|@@@|'.

Your final question was "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?" and here's an answer to that. Sorry I couldn't be of more help to you.

function copyToNewSS()
{
  var dstlocdict = {PickupDate:'A1',ClientName:'B2',Phone:'C3',CakeText:'D4',CakeFlavor:'E5',FrostingFlavor:'F6',FruitFilling:'G7',LinktoPO:'H8'}
  var srcss = SpreadsheetApp.getActiveSpreadsheet();
  var srcsht = srcss.getActiveSheet();
  var dstss = SpreadsheetApp.openById('Insert ID of Destination Spreadsheet');
  var dstsht = dstss.getSheetByName('PO');
  var srcrow = srcsht.getActiveCell().getRow();
  if((srcrow - 1)==0 || srcrow > srcsht.getLastRow()){SpreadsheetApp.getUi().alert('Error: Invalid Data Row');return;}
  var srcdatR = srcsht.getDataRange();
  var srcdatA = srcdatR.getValues();
  var srcdatdict = {};
  var srcnumcols = srcsht.getLastColumn();
  var s = '{';
  for(var i = 0;i < srcdatA[srcrow - 1].length; i++)
  {
    srcdatdict[srcdatA[0][i]]= srcdatA[srcrow-1][i];
    if(i>0)s += ', ';
    s += srcdatA[0][i] + ':' + srcdatA[srcrow-1][i];
  }
  myUtilities.dispStatus('Source Data Dictionary for Row ' + srcrow , s, 800, 400)  
  for(var key in srcdatdict)
  {
    dstsht.getRange(dstlocdict[key]).setValue(srcdatdict[key]); 
  }
}