Add new row and fill with self labling formula

Question

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();
}

score 0 · Accepted Answer · answered Nov 03 '17 at 18:05

0

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

cell.setFormula("getUrl");

the following should be wrote:

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

answered Nov 03 '17 at 18:05

Rubén

34,714
9
70
166

Thanks for the help. 'cell.setFormula()' didn't work used 'cell.setValue("=sheetName()");' – Chris Shortreed Nov 04 '17 at 05:45
Using setValue() to pass formulas isn't reliable (see [setValue to QUERY() formula doesn't work?](https://stackoverflow.com/q/3613674/1595451)) What do you mind byd "didn't work"? Did you get an error message? – Rubén Nov 04 '17 at 14:43
Tried 'cell.setFormula()' no error this time Thanks for the tip. – Chris Shortreed Nov 05 '17 at 02:48
When I tried this code in a second tab I received the error message "The coordinates or dimensions of the range are invalid" – Chris Shortreed Nov 15 '17 at 07:56
@ChrisShortreed: The question include several functions. Which one are you talking about? Is the error shown on a cell? – Rubén Nov 15 '17 at 17:06

Add new row and fill with self labling formula

1 Answers1