google-apps-scriptimportgoogle-sheetscallablenewrow

Add new row and fill with self labling formula


I'm trying to use importrange() to group all of my Google form results. I would like to add at the top of the Google form response sheet a row with, URL, Workbook name, Sheet name, and Date code to easily copy and paste into a master workbook.

I am trying to work toward an easy to make masters workbook which will populate with all of the students results, something that will be easy for other teachers to use and sort data.

I have been learning coding for less than a month and have put together pieces from of other's work, but am getting error messages

"ReferenceError: "sheet" is not defined.

I attached the Google form results with sheet labels in second row. https://docs.google.com/spreadsheets/d/1lW8xkSbrgDm-UG83nCnp_9p3NWvP5bnlaCpENDCgQd4/edit?usp=sharing

I currently have working and getUrl(), sheetName(), and tabName(), and an onOpen() addRow(). I can not get the formula's into the new cells.

var ss = SpreadsheetApp.getActive();

function onOpen() {
  var menu = [{name:"Add New 2nd Row", functionName:"addRow"}];
  ss.addMenu("Extra", menu);
}

function addRow() {
  var sh = ss.getActiveSheet(), lRow = 1; 
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow, 1);
  var cell = sheet.getRange("A2");
  cell.setFormula("getUrl");
  var cell = sheet.getRange("B2");
  cell.setFormula("sheetName");
  var cell = sheet.getRange("C2");
  cell.setFormula("tabName");
}

function getUrl() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SS.getActiveSheet();
  var url = '';
  url += SS.getUrl();
  url += '#gid=';
  url += ss.getSheetId(); 
  return url;
}

function sheetName() {
  var SS = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SS.getActiveSheet();
  return SpreadsheetApp.getActive().getName();
}

function tabName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ss = ss.getActiveRange().getSheet();
  return ss.getName();
}

Solution

  • Formulas should start with = and custom functions should have parenthesis, so instead of

    cell.setFormula("getUrl");
    

    the following should be wrote:

    cell.setFormula("=getUrl()");