Embedded Files
const CONFIG = {
SPREADSHEET_ID: '1eRJCf8b0oMZpapWldSA9To8ZjJylRwrIE8tRDOncRlE',
SENTENCE_SHEET: 'Zinnen',
SETTINGS_SHEET: 'Settings',
FAVORITES_SHEET: 'Favorieten'
};
function doGet() {
return HtmlService
.createTemplateFromFile('Index')
.evaluate()
.setTitle('1000 Zinnen · Presentator')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function getSpreadsheet_() {
return SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
}
function setupSheets() {
const ss = getSpreadsheet_();
let sentenceSheet = ss.getSheetByName(CONFIG.SENTENCE_SHEET);
if (!sentenceSheet) {
sentenceSheet = ss.insertSheet(CONFIG.SENTENCE_SHEET);
}
if (sentenceSheet.getLastRow() === 0) {
sentenceSheet.getRange(1, 1, 1, 7).setValues([[
'ID',
'Zin',
'Actief',
'Favoriet',
'Notitie',
'Aangemaakt',
'Gewijzigd'
]]);
}
let settingsSheet = ss.getSheetByName(CONFIG.SETTINGS_SHEET);
if (!settingsSheet) {
settingsSheet = ss.insertSheet(CONFIG.SETTINGS_SHEET);
settingsSheet.getRange(1, 1, 1, 3).setValues([[
'Key',
'Value',
'Gewijzigd'
]]);
}
let favoritesSheet = ss.getSheetByName(CONFIG.FAVORITES_SHEET);
if (!favoritesSheet) {
favoritesSheet = ss.insertSheet(CONFIG.FAVORITES_SHEET);
favoritesSheet.getRange(1, 1, 1, 6).setValues([[
'ID',
'Zin',
'Notitie',
'Toegevoegd',
'Naam',
'Telefoon'
]]);
}
return {
ok: true,
message: 'Sheets gecontroleerd en klaargezet.'
};
}
function getSentences() {
setupSheets();
const sheet = getSpreadsheet_().getSheetByName(CONFIG.SENTENCE_SHEET);
const lastRow = sheet.getLastRow();
if (lastRow < 2) {
return [];
}
const values = sheet.getRange(2, 1, lastRow - 1, 7).getValues();
return values
.map(function(row, index) {
return {
id: Number(row[0]) || index + 1,
text: String(row[1] || '').trim(),
active: row[2] !== false,
favorite: row[3] === true,
note: String(row[4] || ''),
created: row[5] || '',
modified: row[6] || ''
};
})
.filter(function(item) {
return item.text;
});
}
function saveSentence(sentence) {
setupSheets();
const sheet = getSpreadsheet_().getSheetByName(CONFIG.SENTENCE_SHEET);
const lastRow = sheet.getLastRow();
if (!sentence.id) {
sentence.id = lastRow;
}
const data = sheet.getRange(2, 1, Math.max(1, lastRow - 1), 7).getValues();
let targetRow = -1;
for (let i = 0; i < data.length; i++) {
if (Number(data[i][0]) === Number(sentence.id)) {
targetRow = i + 2;
break;
}
}
const now = new Date();
if (targetRow === -1) {
targetRow = sheet.getLastRow() + 1;
sheet.getRange(targetRow, 1, 1, 7).setValues([[
Number(sentence.id),
sentence.text,
sentence.active !== false,
sentence.favorite === true,
sentence.note || '',
now,
now
]]);
} else {
const existing = sheet.getRange(targetRow, 1, 1, 7).getValues()[0];
sheet.getRange(targetRow, 1, 1, 7).setValues([[
Number(sentence.id),
sentence.text,
sentence.active !== false,
sentence.favorite === true,
sentence.note || '',
existing[5] || now,
now
]]);
}
return {
ok: true,
sentence: sentence
};
}
function updateSentenceText(id, text) {
const sheet = getSpreadsheet_().getSheetByName(CONFIG.SENTENCE_SHEET);
if (!sheet) {
setupSheets();
return updateSentenceText(id, text);
}
const lastRow = sheet.getLastRow();
if (lastRow < 2) {
return false;
}
const ids = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
for (let i = 0; i < ids.length; i++) {
if (Number(ids[i][0]) === Number(id)) {
sheet.getRange(i + 2, 2).setValue(text);
sheet.getRange(i + 2, 7).setValue(new Date());
return true;
}
}
return false;
}
function setFavorite(id, favorite) {
const sheet = getSpreadsheet_().getSheetByName(CONFIG.SENTENCE_SHEET);
if (!sheet) {
setupSheets();
return setFavorite(id, favorite);
}
const lastRow = sheet.getLastRow();
if (lastRow < 2) {
return false;
}
const ids = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
for (let i = 0; i < ids.length; i++) {
if (Number(ids[i][0]) === Number(id)) {
sheet.getRange(i + 2, 4).setValue(Boolean(favorite));
sheet.getRange(i + 2, 7).setValue(new Date());
syncFavorite_(id, favorite);
return true;
}
}
return false;
}
function syncFavorite_(id, favorite) {
const ss = getSpreadsheet_();
const source = ss.getSheetByName(CONFIG.SENTENCE_SHEET);
const target = ss.getSheetByName(CONFIG.FAVORITES_SHEET);
if (!target) {
setupSheets();
return syncFavorite_(id, favorite);
}
const sourceLast = source.getLastRow();
if (sourceLast < 2) {
return;
}
const values = source.getRange(2, 1, sourceLast - 1, 7).getValues();
let sentence = null;
for (let i = 0; i < values.length; i++) {
if (Number(values[i][0]) === Number(id)) {
sentence = values[i];
break;
}
}
if (!sentence) {
return;
}
const targetLast = target.getLastRow();
if (targetLast >= 2) {
const favorites = target.getRange(2, 1, targetLast - 1, 6).getValues();
for (let i = 0; i < favorites.length; i++) {
if (Number(favorites[i][0]) === Number(id)) {
if (!favorite) {
target.deleteRow(i + 2);
}
return;
}
}
}
if (favorite) {
target.appendRow([
sentence[0],
sentence[1],
sentence[4],
new Date(),
'',
''
]);
}
}
function getSettings() {
setupSheets();
const sheet = getSpreadsheet_().getSheetByName(CONFIG.SETTINGS_SHEET);
const lastRow = sheet.getLastRow();
const defaults = {
name: '',
phone: '',
site: '',
slogan: 'Accepteer nu wat is, verander op jouw manier wat kan.',
note: '',
speed: 650,
repeats: 3,
fontSize: 58,
wordColor: '#ffffff',
background: '#111827',
animation: 'fade',
fullscreen: false
};
if (lastRow < 2) {
return defaults;
}
const values = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
values.forEach(function(row) {
const key = String(row[0] || '');
if (key) {
defaults[key] = row[1];
}
});
return defaults;
}
function saveSettings(settings) {
setupSheets();
const sheet = getSpreadsheet_().getSheetByName(CONFIG.SETTINGS_SHEET);
const keys = Object.keys(settings);
if (sheet.getLastRow() > 1) {
sheet.getRange(
2,
1,
sheet.getLastRow() - 1,
3
).clearContent();
}
const rows = keys.map(function(key) {
return [
key,
settings[key],
new Date()
];
});
if (rows.length) {
sheet.getRange(2, 1, rows.length, 3).setValues(rows);
}
return true;
}
function seedStructure() {
setupSheets();
const sheet = getSpreadsheet_().getSheetByName(CONFIG.SENTENCE_SHEET);
const lastRow = sheet.getLastRow();
if (lastRow < 2) {
return {
ok: false,
count: 0,
message: 'De sheet Zinnen is nog leeg. Plak de 1000 zinnen in kolom B en voer daarna normalizeSentences() uit.'
};
}
return normalizeSentences();
}
function normalizeSentences() {
setupSheets();
const sheet = getSpreadsheet_().getSheetByName(CONFIG.SENTENCE_SHEET);
const lastRow = sheet.getLastRow();
if (lastRow < 2) {
return {
ok: false,
count: 0,
message: 'Geen zinnen gevonden.'
};
}
const values = sheet.getRange(2, 1, lastRow - 1, 7).getValues();
const output = [];
values.forEach(function(row, index) {
const text = String(row[1] || '').trim();
if (!text) {
return;
}
output.push([
index + 1,
text,
row[2] === false ? false : true,
row[3] === true,
row[4] || '',
row[5] || new Date(),
new Date()
]);
});
if (output.length) {
sheet.getRange(2, 1, output.length, 7).setValues(output);
}
if (output.length >= 1000) {
sheet.getRange(1001, 2).setValue(
'Accepteer nu wat is, verander op jouw manier wat kan.'
);
}
return {
ok: true,
count: output.length,
message: output.length + ' zinnen verwerkt.'
};
}
function importSentencesFromText(text) {
setupSheets();
const sheet = getSpreadsheet_().getSheetByName(CONFIG.SENTENCE_SHEET);
const lines = String(text || '')
.split(/\r?\n/)
.map(function(line) {
return line.trim();
})
.filter(function(line) {
return line;
});
if (!lines.length) {
return {
ok: false,
count: 0
};
}
const rows = lines.map(function(line, index) {
return [
index + 1,
line.replace(/^\d+[\.\)]\s*/, ''),
true,
false,
'',
new Date(),
new Date()
];
});
sheet.clearContents();
sheet.getRange(1, 1, 1, 7).setValues([[
'ID',
'Zin',
'Actief',
'Favoriet',
'Notitie',
'Aangemaakt',
'Gewijzigd'
]]);
sheet.getRange(2, 1, rows.length, 7).setValues(rows);
if (rows.length >= 1000) {
sheet.getRange(1001, 2).setValue(
'Accepteer nu wat is, verander op jouw manier wat kan.'
);
}
return {
ok: true,
count: rows.length
};
}
function getFavorites() {
const sheet = getSpreadsheet_().getSheetByName(CONFIG.FAVORITES_SHEET);
if (!sheet || sheet.getLastRow() < 2) {
return [];
}
return sheet
.getRange(2, 1, sheet.getLastRow() - 1, 6)
.getValues()
.map(function(row) {
return {
id: Number(row[0]),
text: String(row[1] || ''),
note: String(row[2] || ''),
added: row[3] || '',
name: String(row[4] || ''),
phone: String(row[5] || '')
};
});
}
Page updated
Google Sites
Report abuse