I am attempting to write a script in Google Sheets for a timesheet spreadsheet. I am trying to add a functionality wherein a user can create a new sheet (for the next pay period) by using a custom menu and prompt. The code below creates this menu and attempts to have the new sheet named whatever the user inputs into the prompt. For example, entering 7/24/17 in the prompt dialogue should create a new sheet based upon a template sheet and name the new sheet "7/24/17." Additionally, I plan on adding a function for inserting the user input (7/24/17) into cell D7, which then triggers a function in cell F7 that calculates the end date of the pay period.

Here is a copy of the spreadsheet: https://drive.google.com/open?id=1MfnrMDZosAiyc4yBgrQiDGVZyiHNLwobT5q92O2VxTo

Here is the code (under NewSheet.gs in Script Editor):

    function onOpen() {
  var menu = [{name: "Create new timesheet", functionName: "newSheet"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("New timesheet", menu);
}

function newSheet() {
  var sheetName = showPrompt();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = ss.getSheetByName("Template")
  ss.insertSheet(sheetName, 0, {template: templateSheet});
}

function showPrompt() {
  var ui = SpreadsheetApp.getUi(); 
  var result = ui.prompt(
      'New timesheet',
      'What date does the pay period begin?:',
      ui.ButtonSet.OK_CANCEL);
  return response.getSelectedText()
}

I am very new to Javascript and coding in general, so I apologize if my question has a simple solution (I have tried searching and experimenting). When I run the script, I get an error stating "ReferenceError: "response" is not defined." I cannot figure out how to get the showPrompt function to return a value containing the user response from the prompt. Theoretically (if I understand this correctly), getting the showPrompt function to return this value would allow me to call on the showPrompt function from the sheetName variable and have sheetName recognize the user input as the name of the new sheet. Is the showPrompt function limited to only generating a prompt and unable to return the user response? Not sure if I need a separate function or more code for this.

  • So, the variable you called result should be response. – user135384 Jul 24 '17 at 16:21
  • That makes sense. I am teaching myself as I go - thanks for the help. – Mike Cahill Jul 25 '17 at 17:30

So var result in the last function should actually be 'var response.' I ended up coding the whole process differently in case anyone is looking to do something similar. This is what worked:

function onOpen() {
  var menu = [{name: "Create new timesheet", functionName: "newSheet"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("New Timesheet", menu);
}

function newSheet() {
  var sheetName = Browser.inputBox('New Timesheet', 'What date does the next pay period begin? (mm/dd/yy)', Browser.Buttons.OK_CANCEL);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = ss.getSheetByName("Template")
  ss.insertSheet(sheetName, 0, {template: templateSheet});
  var s = SpreadsheetApp.getActive()
        .getSheets()
        .forEach(function (s) {
            s.getRange('D7')
                .setValue(s.getName())
        })
}

This also includes a method for inserting the user input into the cell as I stated in my original post.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.