My 1st question in Stack Overflow. I have a Google Spreadsheet with 4 individual sheets.
Sheet1 is responses from GForm. Sheet2,3,4 have same data but in 3 languages English, Tamil, Hindi and named English, Tamil, Hindi.
At present the Script takes cell value date from Sheet1, matches the data in the language Tamil sheet, mail merges with the correct language Tamil GDoc and emails it.
Now, I cannot figure out how to make the Script pick a different language Gsheet, one of the 3, depending upon the evalues4 from Form input, which is a Radio Button.
Can someone help me to figure it out? Select appropriate language Gsheet using ss.getSheetByName and GDoc using DriveApp.getFileById based on Form response evalues4.
Here is the code and which works fine, if I input just one language by name/sheetID/DocID.
//Get information from the form and set as variables
function onFormSubmit(e)
{
var Time = e.values[0];
var email = e.values[1];
var fog = e.values[2];
var mog = e.values[3];
var lang = e.values[4];
var do_date = e.values[5];
//Match chosen date from data sheet & get Row Number
function findInColumn() {
var data = do_date;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName("Tamil");
var range = sheet1.getRange(1,1,sheet1.getLastRow(),1);
var values= range.getValues();
var row = 0;
while ( values[row] && values[row][0] !== data ) {
row++;
}
if (values[row][0] === data)
return row+1;
else
return -1;
}
//Use above Row number to load in Range, get values, assign to variable cells
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet3 = ss.getSheetByName("Tamil");
// Get range of cells in All columns and All rows with entries
var dataRange = sheet3.getRange(findInColumn(), 1, 1, sheet3.getLastColumn());
// Get entries for each row in the Range.
var data = dataRange.getValues();
// Set each value to a unique variable
for (i in data) {
var cell = data[i];
// Get document template, copy it as a new doc with Name and email, and save the id
var copyId = DriveApp.getFileById("1Nxxxxxxxxxxxxxx2k")
Sorry, completely forgot that I had asked the question here. I solved it the same weekend, by using the If - else [Note: Instead of var ss I used var ssn, and instead of var sheet3 I used var tocopyID in the rewrite]
//Use above Row number to load in Range, get values, assign to variable cells
var ss = SpreadsheetApp.getActiveSpreadsheet();
if (lang === "Tamil"){
var ssn = ss.getSheetByName("Tamil");
var tocopyId = DriveApp.getFileById("1NxxxxTamil");
}
else if (lang === "Hindi"){
var ssn = ss.getSheetByName("Hindi");
var tocopyId = DriveApp.getFileById("Hindi");
}
else if (lang === "English"){
var ssn = ss.getSheetByName("English");
var tocopyId = DriveApp.getFileById("1NxxxxEnglish");
}