/**

* Clear sheet except first row or create new one(empty)

*

* @param {string} Sheetname

* @customfunction

*/

function InitSheet_(sheetname) {

let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);

if (sheet == null) {

SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetname)

//SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname).getRange(1,1).setValue(0);

SpreadsheetApp.flush();

sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);

}

let data = sheet.getDataRange();

let values = data.getValues();

// Extract headers

let headers = [[]]

headers[0] = values[0];

//Clear complete sheet

data.clearContent();

//put headers back

sheet.getRange(1, 1, 1, headers[0].length).setValues(headers);

//atomize actions.

SpreadsheetApp.flush();

}

/**

* dropdown_

* get user list in dropdown menu

* @param {string} sheet, sheetname for dropdown menu

* @param {string} cell, cell for dropdown in sheet

* @param {string} sourcesheet, sheetname of sourse dropdown

* @param {string} label, name of lable of column for source dropdown

* FSQueryStudentScores(studentEmail, sheetname)

*/

function dropdown_(sheet, cell, sourcesheet, label) {

var ss = SpreadsheetApp.getActiveSpreadsheet()

//create grade sheet

if (ss.getSheetByName("Grade") == null) { ss.insertSheet("Grade") }

SpreadsheetApp.flush()

let Sourcesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sourcesheet);

let Sheeto=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet);

let DropDowncell = Sheeto.getRange(cell)

DropDowncell.setValue("")

let values = Sourcesheet.getDataRange().getValues();

// Extract headers

let headers = values[0];

// find columnheader

let col = headers.indexOf(label)+1

let firstRow = 2

let ListRange = Sourcesheet.getRange(firstRow, col,Sourcesheet.getLastRow() - firstRow + 1, col);

let rule= SpreadsheetApp.newDataValidation().requireValueInRange(ListRange).build();

DropDowncell.setDataValidation(rule)

SpreadsheetApp.flush()

}

/**

* Trims all of the unused rows and columns outside of selected data range.

*/

function cropSheet(sheetname) {

const dataRange = SpreadsheetApp.getActive().getSheetByName(sheetname).getDataRange()

// const dataRange = SpreadsheetApp.getActiveSheet().getDataRange();

const sheet = dataRange.getSheet();

let numRows = dataRange.getNumRows();

let numColumns = dataRange.getNumColumns();

const maxRows = sheet.getMaxRows();

const maxColumns = sheet.getMaxColumns();

const numFrozenRows = sheet.getFrozenRows();

const numFrozenColumns = sheet.getFrozenColumns();

// If last data row is less than maximium row, then deletes rows after the last data row.

if (numRows < maxRows) {

numRows = Math.max(numRows, numFrozenRows + 1); // Don't crop empty frozen rows.

sheet.deleteRows(numRows + 1, maxRows - numRows);

}

// If last data column is less than maximium column, then deletes columns after the last data column.

if (numColumns < maxColumns) {

numColumns = Math.max(numColumns, numFrozenColumns + 1); // Don't crop empty frozen columns.

sheet.deleteColumns(numColumns + 1, maxColumns - numColumns);

}

}