CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Apurva Desai
Apurva Desai

Reputation: 9

Google sheet automatically calculation in other sheet upon receiving new form response

I have a form which records employee vacation data through google form. It has two dates start date and end date. Now I want to list all the date for the range in other sheet upon new form response and copy the form response for all those dates automatically. Please help me on this.

Upvotes: 1

Views: 74

Answers (1)

Cooper
Cooper

Reputation: 64082

Something like this will append it to the new sheet in the new spreadsheet

function onformsubmit(e) {
  const nss = SpreadsheetApp.openById('new spreadsheet id');
  const nsh = nss.getSheetByName('Sheet name');
  nsh.appendRow([e.namedValues['start date name'][0],e.namedValues['end date name'][0]]);
}

You will have to create in onformsubmit trigger for the linked spreadsheet. You will need write access to the new spreadsheet/sheet

Run this to create trigger:

function createTrigger() {
  const ss = SpreadsheetApp.getActive();
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "onformsubmit").length == 0) {
    ScriptApp.newTrigger("onformsubmit").forSpreadsheet(ss).onFormSubmit().create();
  }
}

Upvotes: 1

Related Questions