Starred Gists
Starred Gists订阅

incode, admin spreadsheet script

const properties = PropertiesService.getScriptProperties();
const year = properties.getProperty('year');
function BETWEEN(value, min, max) {
return value >= min && value <= max;
}
/**
* Retrieves all the rows in the active spreadsheet that contain data and logs the
* values for each row.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function readRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
Logger.log(row);
}
};
/**
* Adds a custom menu to the active spreadsheet, containing a single menu item
* for invoking the readRows() function specified above.
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Read Data",
functionName : "readRows"
}];
sheet.addMenu("Script Center Menu", entries);
};
/*====================================================================================================================================*
ImportJSON by Trevor Lohrbeer (@FastFedora)
====================================================================================================================================
Version: 1.1
Project Page: http://blog.fastfedora.com/projects/import-json
Copyright: (c) 2012 by Trevor Lohrbeer
License: GNU General Public License, version 3 (GPL-3.0)
http://www.opensource.org/licenses/gpl-3.0.html
------------------------------------------------------------------------------------------------------------------------------------
A library for importing JSON feeds into Google spreadsheets. Functions include:
ImportJSON For use by end users to import a JSON feed from a URL
ImportJSONAdvanced For use by script developers to easily extend the functionality of this library
Future enhancements may include:
- Support for a real XPath like syntax similar to ImportXML for the query parameter
- Support for OAuth authenticated APIs
Or feel free to write these and add on to the library yourself!
------------------------------------------------------------------------------------------------------------------------------------
Changelog:
1.1 Added support for the noHeaders option
1.0 Initial release
*====================================================================================================================================*/
/**
* Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
* a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
* the JSON feed. The remaining rows contain the data.
*
* By default, data gets transformed so it looks more like a normal data import. Specifically:
*
* - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
* of the rows representing their parent elements.
* - Values longer than 256 characters get truncated.
* - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case.
*
* To change this behavior, pass in one of these values in the options parameter:
*
* noInherit: Don't inherit values from parent elements
* noTruncate: Don't truncate values
* rawHeaders: Don't prettify headers
* noHeaders: Don't include headers, only the data
* debugLocation: Prepend each value with the row & column it belongs in
*
* For example:
*
* =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content",
* "noInherit,noTruncate,rawHeaders")
*
* @param {url} the URL to a public JSON feed
* @param {query} a comma-separated lists of paths to import. Any path starting with one of these paths gets imported.
* @param {options} a comma-separated list of options that alter processing of the data
*
* @return a two-dimensional array containing the data, with the first row containing headers
* @customfunction
**/
function ImportJSON(url, query, options) {
return ImportJSONAdvanced(url, query, options, includeXPath_, defaultTransform_);
}
/**
* An advanced version of ImportJSON designed to be easily extended by a script. This version cannot be called from within a
* spreadsheet.
*
* Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create
* a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in
* the JSON feed. The remaining rows contain the data.
*
* Use the include and transformation functions to determine what to include in the import and how to transform the data after it is
* imported.
*
* For example:
*
* =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json",
* "/feed/entry",
* function (query, path) { return path.indexOf(query) == 0; },
* function (data, row, column) { data[row][column] = data[row][column].toString().substr(0, 100); } )
*
* In this example, the import function checks to see if the path to the data being imported starts with the query. The transform
* function takes the data and truncates it. For more robust versions of these functions, see the internal code of this library.
*
* @param {url} the URL to a public JSON feed
* @param {query} the query passed to the include function
* @param {options} a comma-separated list of options that may alter processing of the data
* @param {includeFunc} a function with the signature func(query, path, options) that returns true if the data element at the given path
* should be included or false otherwise.
* @param {transformFunc} a function with the signature func(data, row, column, options) where data is a 2-dimensional array of the data
* and row & column are the current row and column being processed. Any return value is ignored. Note that row 0
* contains the headers for the data, so test for row==0 to process headers only.
*
* @return a two-dimensional array containing the data, with the first row containing headers
**/
function ImportJSONAdvanced(url, query, options, includeFunc, transformFunc) {
var jsondata = UrlFetchApp.fetch(url);
var object = JSON.parse(jsondata.getContentText());
return parseJSONObject_(object, query, options, includeFunc, transformFunc);
}
/**
* Encodes the given value to use within a URL.
*
* @param {value} the value to be encoded
*
* @return the value encoded using URL percent-encoding
*/
function URLEncode(value) {
return encodeURIComponent(value.toString());
}
/**
* Parses a JSON object and returns a two-dimensional array containing the data of that object.
*/
function parseJSONObject_(object, query, options, includeFunc, transformFunc) {
var headers = new Array();
var data = new Array();
if (query && !Array.isArray(query) && query.toString().indexOf(",") != -1) {
query = query.toString().split(",");
}
if (options) {
options = options.toString().split(",");
}
parseData_(headers, data, "", 1, object, query, options, includeFunc);
parseHeaders_(headers, data);
transformData_(data, options, transformFunc);
return hasOption_(options, "noHeaders") ? (data.length > 1 ? data.slice(1) : new Array()) : data;
}
/**
* Parses the data contained within the given value and inserts it into the data two-dimensional array starting at the rowIndex.
* If the data is to be inserted into a new column, a new header is added to the headers array. The value can be an object,
* array or scalar value.
*
* If the value is an object, it's properties are iterated through and passed back into this function with the name of each
* property extending the path. For instance, if the object contains the property "entry" and the path passed in was "/feed",
* this function is called with the value of the entry property and the path "/feed/entry".
*
* If the value is an array containing other arrays or objects, each element in the array is passed into this function with
* the rowIndex incremeneted for each element.
*
* If the value is an array containing only scalar values, those values are joined together and inserted into the data array as
* a single value.
*
* If the value is a scalar, the value is inserted directly into the data array.
*/
function parseData_(headers, data, path, rowIndex, value, query, options, includeFunc) {
var dataInserted = false;
if (isObject_(value)) {
for (key in value) {
if (parseData_(headers, data, path + "/" + key, rowIndex, value[key], query, options, includeFunc)) {
dataInserted = true;
}
}
} else if (Array.isArray(value) && isObjectArray_(value)) {
for (var i = 0; i < value.length; i++) {
if (parseData_(headers, data, path, rowIndex, value[i], query, options, includeFunc)) {
dataInserted = true;
rowIndex++;
}
}
} else if (!includeFunc || includeFunc(query, path, options)) {
// Handle arrays containing only scalar values
if (Array.isArray(value)) {
value = value.join();
}
// Insert new row if one doesn't already exist
if (!data[rowIndex]) {
data[rowIndex] = new Array();
}
// Add a new header if one doesn't exist
if (!headers[path] && headers[path] != 0) {
headers[path] = Object.keys(headers).length;
}
// Insert the data
data[rowIndex][headers[path]] = value;
dataInserted = true;
}
return dataInserted;
}
/**
* Parses the headers array and inserts it into the first row of the data array.
*/
function parseHeaders_(headers, data) {
data[0] = new Array();
for (key in headers) {
data[0][headers[key]] = key;
}
}
/**
* Applies the transform function for each element in the data array, going through each column of each row.
*/
function transformData_(data, options, transformFunc) {
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j++) {
transformFunc(data, i, j, options);
}
}
}
/**
* Returns true if the given test value is an object; false otherwise.
*/
function isObject_(test) {
return Object.prototype.toString.call(test) === '[object Object]';
}
/**
* Returns true if the given test value is an array containing at least one object; false otherwise.
*/
function isObjectArray_(test) {
for (var i = 0; i < test.length; i++) {
if (isObject_(test[i])) {
return true;
}
}
return false;
}
/**
* Returns true if the given query applies to the given path.
*/
function includeXPath_(query, path, options) {
if (!query) {
return true;
} else if (Array.isArray(query)) {
for (var i = 0; i < query.length; i++) {
if (applyXPathRule_(query[i], path, options)) {
return true;
}
}
} else {
return applyXPathRule_(query, path, options);
}
return false;
};
/**
* Returns true if the rule applies to the given path.
*/
function applyXPathRule_(rule, path, options) {
return path.indexOf(rule) == 0;
}
/**
* By default, this function transforms the value at the given row & column so it looks more like a normal data import. Specifically:
*
* - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values
* of the rows representing their parent elements.
* - Values longer than 256 characters get truncated.
* - Values in row 0 (headers) have slashes converted to spaces, common prefixes removed and the resulting text converted to title
* case.
*
* To change this behavior, pass in one of these values in the options parameter:
*
* noInherit: Don't inherit values from parent elements
* noTruncate: Don't truncate values
* rawHeaders: Don't prettify headers
* debugLocation: Prepend each value with the row & column it belongs in
*/
function defaultTransform_(data, row, column, options) {
if (!data[row][column]) {
if (row < 2 || hasOption_(options, "noInherit")) {
data[row][column] = "";
} else {
data[row][column] = data[row-1][column];
}
}
if (!hasOption_(options, "rawHeaders") && row == 0) {
if (column == 0 && data[row].length > 1) {
removeCommonPrefixes_(data, row);
}
data[row][column] = toTitleCase_(data[row][column].toString().replace(/[\/\_]/g, " "));
}
if (!hasOption_(options, "noTruncate") && data[row][column]) {
data[row][column] = data[row][column].toString().substr(0, 256);
}
if (hasOption_(options, "debugLocation")) {
data[row][column] = "[" + row + "," + column + "]" + data[row][column];
}
}
/**
* If all the values in the given row share the same prefix, remove that prefix.
*/
function removeCommonPrefixes_(data, row) {
var matchIndex = data[row][0].length;
for (var i = 1; i < data[row].length; i++) {
matchIndex = findEqualityEndpoint_(data[row][i-1], data[row][i], matchIndex);
if (matchIndex == 0) {
return;
}
}
for (var i = 0; i < data[row].length; i++) {
data[row][i] = data[row][i].substring(matchIndex, data[row][i].length);
}
}
/**
* Locates the index where the two strings values stop being equal, stopping automatically at the stopAt index.
*/
function findEqualityEndpoint_(string1, string2, stopAt) {
if (!string1 || !string2) {
return -1;
}
var maxEndpoint = Math.min(stopAt, string1.length, string2.length);
for (var i = 0; i < maxEndpoint; i++) {
if (string1.charAt(i) != string2.charAt(i)) {
return i;
}
}
return maxEndpoint;
}
/**
* Converts the text to title case.
*/
function toTitleCase_(text) {
if (text == null) {
return null;
}
return text.replace(/\w\S*/g, function(word) { return word.charAt(0).toUpperCase() + word.substr(1).toLowerCase(); });
}
/**
* Returns true if the given set of options contains the given option.
*/
function hasOption_(options, option) {
return options && options.indexOf(option) >= 0;
}
function addRowsToSheet(sheet, rows) {
var row = rows.length;
var column = rows[0].length;
sheet.getRange(sheet.getLastRow()+1, 1, row, column).setValues(rows);
}
function getInvoices() {
const properties = PropertiesService.getScriptProperties();
var url = "https://api-v2.fattureincloud.it";
var endpoint = "/c/36/issued_documents";
var headers = {
"Content-Type": "application/json",
"Authorization": `Bearer ${properties.getProperty('fattureincloud_access_token')}`
};
var options = {
"method": "get",
"headers": headers,
};
var queryParams = [
"type=" + "invoice",
"per_page="+ 100,
"fieldset="+ "detailed",
"sort=-number,-date",
"q="+encodeURIComponent(`date>'${year-1}-01-01'`)
];
var pageKey = "page"
var pageNum = 1;
var nextPageUrl;
var data = {},
output = [];
do {
var currentUrl = url + endpoint + "?" + queryParams.join("&");
currentUrl += "&" + pageKey + "=" + pageNum;
var response = UrlFetchApp.fetch(currentUrl, options);
data = JSON.parse(response.getContentText());
output.push(...data.data)
pageNum++;
nextPageUrl = data.next_page_url;
} while (nextPageUrl);
return output;
}
function getPaymentsDue() {
const invoices = getInvoices();
console.log(invoices)
const payments = [];
invoices.forEach( invoice => payments.push(...invoice.payments_list))
const paymentsDue = payments.filter( payment => {
return (new Date(payment.due_date) < (new Date()) && payment.status == "not_paid")
})
console.log(paymentsDue)
const amountDue = paymentsDue.reduce( (acc, cur) => acc+=cur.amount , 0)
console.log(amountDue)
return amountDue;
}
function revenueYear(year=null) {
if(!year) year = (new Date()).getFullYear();
const invoices = getInvoices()
.filter( i => i.year == year)
const revenue = invoices.reduce((sum, i) => {return sum += i.amount_net}, 0)
console.log(invoices)
return revenue;
}
function flycalPlanningDefaultOptions() {
return {
from: (new Date().toISOString().slice(0, 10) + "T00:00:00Z"),
calendar: "incode - antonio|incode - luca|incode - matteo|incode - andrea",
to: "2026-12-31",
keys: "placeholder|rui|composer|solver|sanlorenzo|fraghe|comoonboat|vitaloni|hangerstore|danesi",
}
}
function getEventHoursByKeys(options={}) {
console.log("getEventHoursByKeys", options)
options = {...flycalPlanningDefaultOptions(), ...options}
const url = `https://app.flycal.it/api/flycal/search`;
const body = {
from: options.from, //aggiunto il from
to: options.to,
calendar: options.calendar, //aggiunto calendario
description: options.keys,
groupBy: options.keys,
}
var options = {
"method": "get",
"headers": headers = {
"Content-Type": "application/json",
"Authorization": `Bearer ${properties.getProperty('flycal_api_token')}`
},
payload: JSON.stringify(body),
};
const response = UrlFetchApp.fetch(url, options);
const data = JSON.parse(response.getContentText());
const output = {};
data.groups.forEach(g => {
output[g.key] = g.total_hours
})
return data;
}
function parseGroupsHours_(data) {
const rows = [];
const test = (data.groups || []).map(g => [
g.key,
g.events_found,
(g.items || []).length,
[...new Set((g.items || []).map(i => i.calendarSummary))]
])
console.log("test", test);
(data.groups || []).forEach(group => {
const totals = {}; // { calendarSummary: ore }
(group.items || []).forEach(item => {
const start = new Date(item.start.dateTime);
const end = new Date(item.end.dateTime);
const hours = (end - start) / (1000 * 60 * 60);
const cal = item.calendarSummary || item.calendarId || "unknown";
totals[cal] = (totals[cal] || 0) + hours;
});
Object.entries(totals).forEach(([cal, hours]) => {
const h = Math.round(hours * 100) / 100;
const d = hours/8;
rows.push([group.key, cal, h, d ]);
});
});
return rows;
}
// Il floglio chiama la fuzione FLYCAL_HOURS passando tra argomenti non un options... riportati nella firma del metodo e definiti al suo interno
function FLYCAL_HOURS(calendar, to, keys) {
const options = flycalPlanningDefaultOptions();
if (calendar) options.calendar = calendar;
if (to) options.to = to;
if (keys) options.keys = keys;
const cache = CacheService.getScriptCache();
//const cacheKey = `CacheFlycalHours-${options.to}-${options.keys}`;
const raw = [options.from, options.to, options.calendar, options.keys].join('§');
const cacheKey = 'flycal-' + Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, raw));
const cached = cache.get(cacheKey);
if (cached) {
console.log("cache HIT", cached)
return JSON.parse(cached);
}
const data = getEventHoursByKeys(options)
const rows = parseGroupsHours_(data)
cache.put(cacheKey, JSON.stringify(rows), 300); // 300 sec = 5 min
console.log(rows)
return rows;
}
function testFlycal() {
const rows = FLYCAL_HOURS(
'incode - antonio|incode - andrea|incode - matteo|incode - luca',
'2026-12-31',
'placeholder|rui|composer|solver|sanlorenzo|fraghe|comoonboat|vitaloni|hangerstore|danesi.revamp'
);
console.log(JSON.stringify(rows, null, 2));
}
function testFormat(val) {
return 1;
}
const CONFIG_WORK = {
CODE_CELL: 'a11', // cella da cui leggere il numero "code"
CODE_SHEET: null, // null = foglio corrente; oppure 'NomeFoglio' se il code sta altrove
GROUP_COLUMN: 4, // indice di una colonna DENTRO il gruppo da espandere (1 = A, 5 = E...)
GROUP_DEPTH: 1, // profondità del gruppo (1 = gruppo di primo livello)
TRIGGER_CELL: 'D1' // cella con la casella di controllo (opzionale, vedi onEdit)
};
/**
* Valori da inserire nella nuova riga.
* Il primo elemento viene sostituito con il code letto dalla cella.
*/
function buildRow(code) {
return [
code,
'work',
'aperto', //state
'', //handle
1000,
'', //future income?
'client', //client
'', //rif cliente / po
'direct', //ownership
'website', //tags
'none', //data access?
'no', //admin_access?
'none', // dpa_status
2026, //year
];
}
/**
* Estrae il numero da valori come 123, "123", "next: 123", "next:123".
* Restituisce null se non trova un numero.
*/
function parseCode(value) {
if (typeof value === 'number') return value;
// Versione senza regexp: prende quello che c'è dopo l'ultimo ":"
const part = String(value).split(':').pop().trim();
const n = Number(part);
if (part !== '' && !isNaN(n)) return n;
// Fallback con regexp: primo gruppo di cifre ovunque nel testo
const m = String(value).match(/\d+/);
return m ? Number(m[0]) : null;
}
/**
* Funzione principale: da assegnare al pulsante.
*/
function newWork() {
const ui = SpreadsheetApp.getUi();
const answer = ui.alert('Conferma', 'Confirm new row?', ui.ButtonSet.YES_NO);
if (answer !== ui.Button.YES) return;
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
// 1. Legge il code
const codeSheet = CONFIG_WORK.CODE_SHEET ? ss.getSheetByName(CONFIG_WORK.CODE_SHEET) : sheet;
const code = parseCode(codeSheet.getRange(CONFIG_WORK.CODE_CELL).getValue());
if (code === '' || isNaN(Number(code))) {
SpreadsheetApp.getUi().alert('Nella cella ' + CONFIG_WORK.CODE_CELL + ' non c\'è un numero valido.');
return;
}
// 2. Inserisce la riga in fondo al foglio
sheet.appendRow(buildRow(Number(code)));
const lastRow = sheet.getLastRow();
// 3. Espande il gruppo di colonne (se esiste)
try {
const group = sheet.getColumnGroup(CONFIG_WORK.GROUP_COLUMN, CONFIG_WORK.GROUP_DEPTH);
if (group && group.isCollapsed()) {
group.expand();
}
} catch (e) {
// Nessun gruppo su quella colonna: ignora
}
// 4. Scrolla alla nuova riga selezionandola
SpreadsheetApp.flush();
sheet.getRange(lastRow, 1).activate();
}
/**
* Alternativa al pulsante: casella di controllo in CONFIG_WORK.TRIGGER_CELL.
* Quando viene spuntata esegue newWork e si rimette a falso.
*/
function onEdit(e) {
if (!e || !e.range) return;
if (e.range.getA1Notation() !== CONFIG_WORK.TRIGGER_CELL) return;
if (e.value !== 'TRUE') return;
newWork();
e.range.setValue(false);
}
参与讨论