Google Sheets – Clear Contents of Active Sheet

google sheetsgoogle-apps-script

I'm trying to clear the contents of certain cells in the active sheet of my google sheets spreadsheet. Here's what I have so far:

function Reset() {
  var sheet = sheetName();
  var range = sheet.getRange("B4:G53");
  range.clearContent();
}

function sheetName(){ 
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

I get the error:
TypeError: Cannot find function getRange in object Copy of 5.7.18. (line 4, file "Code")

What am I doing wrong? Thanks in advance.

Best Answer

The problem is that your variable sheet doesn't make reference to a Class Sheet object.

Change

return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

Related Solutions

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Google-sheets – How to Process Rows of Google Sheet Singly

When I try to apply this formula to any row after the first row (excluding headers) I get a #NAME? error

The#NAME? error occurs on custom functions when the custom functions use a service, in this case SpreadsheetApp, that requires authorization to run and it is not authorized yet.

To authorize your script,

Go to the script editor by clicking on Tools > Script editor
In the toolbar, click the function selector and select one function
Click the Run or Debug buttons.

See https://developers.google.com/apps-script/overview for further details.

Also, what is the equivalent of console.logging when trying to debug with a google script?

For custom logging use Logger.log(data). For further details, see https://developers.google.com/apps-script/troubleshooting

UPDATE:

Google added Stackdriving Logging support to Google Apps Script so now it's possible to use the Class console for logging messages.

Best Answer

Related Solutions

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

Google-sheets – How to Process Rows of Google Sheet Singly

Related Topic