Google Apps Script - How to add columns (inc formulas and formatting) to a spreadsheet generated from Google Form data?

Question

OK so I think what I need doing is relatively complicated. At the moment we have a form which participants can fill out. We have a script implemented that means every time a form gets submitted it creates a new sheet within the spreadsheet the information is then passed through something similar to an IF function to change the Yes and No answers to 1 and 0. It looks like this:

//onFormSubmit
 function onFormSubmit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses");
  var headings = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var lastRow = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getValues();
  var whoIsItFor = lastRow[0][1];

 if(ss.getSheetByName(whoIsItFor))
{
 var userSheet = ss.getSheetByName(whoIsItFor);
//if not make
} 
else 
{
var userSheet = ss.insertSheet(whoIsItFor);
userSheet.getRange(1, 1, 1, headings[0].length).setValues(headings);
}

// copy submitted data to user's sheet
userSheet.getRange(userSheet.getLastRow()+1, 1, 1, lastRow[0].length).setValues(lastRow);


var columns = new Array('K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', 'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM');

for (var i = 0; i < columns.length; ++i)
{

c = columns[i] + "2";

if (userSheet.getRange(c).getValue() == "Yes") {
  userSheet.getRange(c).setValue("1")
}
else {
  userSheet.getRange(c).setValue("0")
}

}  



}

Now I want to implement the next stage. What I want to happen if for another sheet with various formulas and formatting to be added to the sheet that my current script generates and for the data generated from the form to be inserted within a certain column on that sheet that contains the formulas. I want this to happen every time the original script generates a new sheet. The sheet that contains the various formulas and formatting is contained within the same spreadsheet.

I hope this makes sense.....thank you very much in advance

You request is not very clear to me. Are you asking how to merge two sheets together? If so, what would be the rules for this merge? — Anton Soradoi

Cody Sass Cody Sass · Accepted Answer · 2012-05-30T17:43:28

It sounds like (and I could be wrong) you have a form of True False questions, each form submission moves the data to a new sheet, and now you want to move all the functions and formatting from one "master sheet" and insert it into this newly created sheet.

Couldn't you just create a copy of this "master sheet" you want to import the functions and formatting to? Then you could put in the data from your form submit.

https://developers.google.com/apps-script/service_spreadsheet

I'm going straight off of their API because I haven't tried this before but essentially, you'd make a new sheet like this

//make the new sheet
masterSheet.copyTo("nameOfNewSheet");
//fill data in as usual

If I interpreted your question correctly let me know.

Google Apps Script - How to add columns (inc formulas and formatting) to a spreadsheet generated from Google Form data?

1 Answers