Code QuizsheetToFS_score

/**

* Convert sheetrows to FireStore documents

* @param {string} "collectionpath" collection path

* @param {number} count, number of last records to update if 0=all

* @return {array} values of sheet.

* header row becomes properties

* if score in 3the column, than 2de is email

* docname propery + document name = sheetname+recordnr

*

*/

function QuizsheetToFS_score(collectionpath, count) {

try {

// Script run interactive-> Alerts will work, otherwise not

var DisplayAlerts = true;

try {

var ui = SpreadsheetApp.getUi();

}

catch (err) {

DisplayAlerts = false;

}

//Lock access to vars/sheet from triggers

LockService.getScriptLock().waitLock(300000) //max 6min

// get last know record count

let docProperties = PropertiesService.getDocumentProperties()

//previous versions did not have RecCount

let reccount = (docProperties.getProperty('RecCount') == null) ? 0 : docProperties.getProperty('RecCount')

reccount = parseInt(reccount)

Logger.log(`Previous RecCount=${reccount}`)

// get Firestore db

var firestore = getFireStore()

// Get the table values

var ss = SpreadsheetApp.getActiveSpreadsheet();

// extract name without (response)

var s = ss.getName().split(" (")

var sheetname = s[0]

var sheets = ss.getSheets();

let name = sheets[0].getName();

let formURL = sheets[0].getFormUrl()

// if sheet is not connected to a form, it is no quiz

if (formURL == "" || formURL == null) {

SpreadsheetApp.flush();

LockService.getScriptLock().releaseLock()

if (DisplayAlerts) {

ui.alert(`This sheet is not connected to a form!`);

Logger.log(`This sheet is not connected to a form!`)

}

return false

}

// create sheet to calc % score

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

// Insert formulas

let Sheet0 = sheets[0].getSheetName()

// formula copy Score column from form respose sheet(sheet0)

let formula0 = `={'${Sheet0}'!C1:C}`

// Put array formula in sheet, Different formula for NL or US versions, based on country codes

var Locale = ss.getSpreadsheetLocale()

let formula1 = ''

switch (Locale) {

case "en_GB":

case "en_US":

formula1 = `=ARRAYFORMULA(IF(A2:A="","",INT(left(A2:A,len(A2:A)-find("/",A2:A)-1)/right(A2:A,len(A2:A)-find("/",A2:A)-1)*100)))`

break;

default: //Default list ";" translation by google to ","

case "nl_NL":

formula1 = `=ARRAYFORMULA(IF(A2:A="";"";INT(left(A2:A;len(A2:A)-find("/";A2:A)-1)/right(A2:A;len(A2:A)-find("/";A2:A)-1)*100)))`

}

// set formulas in new sheet

ss.getSheetByName("calc").getRange(1, 1).setValue(formula0)

ss.getSheetByName("calc").getRange(1, 2).setValue("Score")

ss.getSheetByName("calc").getRange(2, 2).setValue(formula1)

SpreadsheetApp.flush()

// get form response URL

let responsesURL = `https://docs.google.com/forms/d/${getFileIdFromDriveUrl(formURL)}/edit#responses`

var values = ss.getSheetByName(name).getDataRange().getValues();

var valuescalc = ss.getSheetByName("calc").getDataRange().getValues();

// Extract headers

var headers = values[0]

// Extract the body

if (headers[1] != "Score" && headers[2] != "Score") {

SpreadsheetApp.flush();

LockService.getScriptLock().releaseLock()

if (DisplayAlerts) {

ui.alert(`The connected form is no quiz!`);

Logger.log(`The connected form is no quiz!`)

}

return false

}

var body = values.slice(1);

var bodycalc = valuescalc.slice(1)

// number of colums to save 3 if emails are collected

var l = (headers[2] == "Score") ? 3 : 2

// number of questions to calculate %

var max = headers.length - l

//Create ISO date string

var date = new Date().toISOString()

// You need an index for looping through the body rows

//Start record to write next from previous saved pointer

var recnr = (count == 0 || count >= body.length) ? 0 : reccount + 1

Logger.log(`recnr=${recnr}`)

//when multiple triggers within a few seconds, multipe record have to be written.

//save reccount for next write

docProperties.setProperty('RecCount', body.length - 1)

Logger.log(`recCountsave=${body.length - 1}`)

//record pointer

let r = recnr

while (r <= body.length - 1) {

// Initialize document obj, put sheet name as field in record (extra action)

let document = {}

document.docname = `${sheetname}_${r + 1}_${body[r][0]}` //docname = sheetname+date+recordnr

document.quizname = `${sheetname}`

document.formURL = formURL

document.responsesURL = responsesURL

document.changed = date

for (c = 0; c < l; c++) {

document[headers[c]] = ``

if (headers[c] == "Score") {

document[headers[c]] = parseInt(bodycalc[r][1])

//document[headers[c]] = parseInt((body[r][c] / max) * 100)

} else {

document[headers[c]] = body[r][c] //Assign cell value to property

}

}

let updatedoc = firestore.updateDocument(`${collectionpath}/${document.docname}`, document, mask = true);

r++

Logger.log(`Updatedoc=${updatedoc}`)

}

SpreadsheetApp.flush();

LockService.getScriptLock().releaseLock()

return true;

} catch (e) {

LockService.getScriptLock().releaseLock()

if (DisplayAlerts) { ui.alert(e); }

Logger.log(e)

}

}