I wonder if any of you kind members would be able to help me rewrite the below code, which currently sends the data from the tab called "Sheet1" ( which is my submission form) to the tab "Database" all within one worksheet. So that the code now will send the submission form data to a separate worksheet containing only the "Database" tab. All below functions are assigned to the respective buttons.
function ClearCell() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var forms = ss.getSheetByName("Sheet1");
var rangesToClear = ["B1" , "C2" , "C3" , "C4" , "C5" , "C6" , "C7" , "B14" , "B15" , "B16" , "B17" ,
"B18" , "B19" , "D14" , "D15" , "D16" , "D17" , "D18" , "D19" ];
for (var i=0; i<rangesToClear.length; i++) {
forms.getRange(rangesToClear[i]).clearContent();
}
}
function SubmitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var forms = ss.getSheetByName("Sheet1");
var datas = ss.getSheetByName("Database");
var values = [[forms.getRange("C2").getValue(),
forms.getRange("C3").getValue(),
forms.getRange("C4").getValue(),
forms.getRange("C5").getValue(),
forms.getRange("C6").getValue(),
forms.getRange("C7").getValue(),
forms.getRange("C8").getValue(),
forms.getRange("B14").getValue(),
forms.getRange("B15").getValue(),
forms.getRange("B16").getValue(),
forms.getRange("B17").getValue(),
forms.getRange("B18").getValue(),
forms.getRange("B19").getValue(),
forms.getRange("D14").getValue(),
forms.getRange("D15").getValue(),
forms.getRange("D16").getValue(),
forms.getRange("D17").getValue(),
forms.getRange("D18").getValue(),
forms.getRange("D19").getValue()]];
datas.getRange(datas.getLastRow()+1,1,1,19).setValues(values);
ClearCell();
}
var SEARCH_COL_INDX = 0;
function Search() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var forms = ss.getSheetByName("Sheet1");
var str = forms.getRange("B1").getValue();
var values = ss.getSheetByName("Database").getDataRange().getValues();
for (var i = 0; i< values.length; i++) {
var row = values[i];
if (row[SEARCH_COL_INDX] == str) {
forms.getRange("C2").setValue(row[19]);
forms.getRange("C3").setValue(row[1]);
forms.getRange("C4").setValue(row[2]);
forms.getRange("C5").setValue(row[3]);
forms.getRange("C6").setValue(row[4]);
forms.getRange("C7").setValue(row[5]);
forms.getRange("C8").setValue(row[6]);
forms.getRange("B14").setValue(row[7]);
forms.getRange("B15").setValue(row[8]);
forms.getRange("B16").setValue(row[9]);
forms.getRange("B17").setValue(row[10]);
forms.getRange("B18").setValue(row[11]);
forms.getRange("B19").setValue(row[12]);
forms.getRange("D14").setValue(row[13]);
forms.getRange("D15").setValue(row[14]);
forms.getRange("D16").setValue(row[15]);
forms.getRange("D17").setValue(row[16]);
forms.getRange("D18").setValue(row[17]);
forms.getRange("D19").setValue(row[18]);
}
}
}
var str = forms.getRange("B1").getValue();
var values = ss.getSheetByName("Data").getDataRange().getValues();
for (var i = 0; i< values.length; i++) {
var row = values[i];
if (row[SEARCH_COL_INDX] == str) {
var INT_R = i+1
var values1 = [[forms.getRange("C2").getValue(),
forms.getRange("C3").getValue(),
forms.getRange("C4").getValue(),
forms.getRange("C5").getValue(),
forms.getRange("C6").getValue(),
forms.getRange("C7").getValue(),
forms.getRange("C8").getValue(),
forms.getRange("B14").getValue(),
forms.getRange("B15").getValue(),
forms.getRange("B16").getValue(),
forms.getRange("B17").getValue(),
forms.getRange("B18").getValue(),
forms.getRange("B19").getValue(),
forms.getRange("D14").getValue(),
forms.getRange("D15").getValue(),
forms.getRange("D16").getValue(),
forms.getRange("D17").getValue(),
forms.getRange("D18").getValue(),
forms.getRange("D19").getValue()]];
datas.getRange(INT_R,1,1,19).setValues(values1);
SpreadsheetApp.getUi().alert('well done ');
ClearCell();
}
}
}
you can use SpreadsheetApp.openById('ID_GOES HERE');
for reference, you may prefer this link https://developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)
function SubmitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var forms = ss.getSheetByName("Sheet1");
var ss_new = SpreadsheetApp.openById('ID_GOES HERE');
var datas = ss_new.getSheetByName("Database");
......