Custom HTML Form posting to different sheets in google sheets

Question

I have 4 form that I need to post to different sheets. I have the following code that with let the forms post but only to the current(Active) sheet but I cannot get it to post to a different sheet.

Another option is if I can have one form and depending what department(Sheet Name) you select it would post to that sheet.

function openDialog1() {
  var html = HtmlService.createHtmlOutputFromFile('form1.html');
    SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .showModalDialog(html, 'Pop up Form');
}
function openDialog2() {
  var html = HtmlService.createHtmlOutputFromFile('form2.html');
    SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .showModalDialog(html, 'Pop up Form');
}
function openDialog3() {
  var html = HtmlService.createHtmlOutputFromFile('form3.html');
    SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .showModalDialog(html, 'Pop up Form');
}
function openDialog4() {
  var html = HtmlService.createHtmlOutputFromFile('form4.html');
    SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
    .showModalDialog(html, 'Pop up Form');
}

function doGet(e){
    return HtmlService.createHtmlOutputFromFile('index').setTitle('Adding Rows');
}

function doPost(e) {
    Logger.log(e);
}

function sendText(data){
      var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow([data.item_number, data.shop_number, data.escalation, data.Hide1, data.notes, data.problem, data.added_by, data.incoive_date, data.location_order, data.user_working, data.CS_rep, data.shipping, data.department]);

    return 'success!';
}

Here is a link to my test Spreadsheet so you can see the HTML and test.

https://docs.google.com/spreadsheets/d/1iWQ40boplJcJmdFg9HNIyOAOrHOjlCRu362LWRdV5y0/edit?usp=sharing

sorry, I thought I had it so anyone can edit. I have that fixed now. everyone should have access now — Joshua Doan
I tried changing the sendText function to call a certain sheet but I think my js code was wrong, I also have it set up where if I edit column (M) it will move the row to the corresponding sheet. but when it is submitted through a form the onEdit function does not apply to it. I was trying to find a way to push them through once every 30 minutes but couldn't figure that out. — Joshua Doan

JSmith JSmith · Accepted Answer · 2018-10-24T21:51:53

as seen on your spreadsheet you are using google.script.run.sendText(data)on each forms. one solution would be to rebuild your function as so.

html

<script>
  google.script.run.withSuccessHandler(function(response) {
    console.log(response);
    google.script.host.close()
    }).sendText("Your Sheet",data);
</script>

GAS

function sendText(sheetName, data){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  sheet.appendRow([data.item_number, data.shop_number, data.escalation, data.Hide1,       data.notes, data.problem, data.added_by, data.incoive_date, data.location_order, data.user_working, data.CS_rep, data.shipping, data.department]);
  return 'success!';
}

You only have to pass as argument your sheet name.

Custom HTML Form posting to different sheets in google sheets

1 Answers