function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var csvMenuEntries = [{ name: 'export as csv files', functionName: 'userActionSaveAllSheetsAsCSV' }];
ss.addMenu('csv', csvMenuEntries);
}
function userActionSaveAllSheetsAsCSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var ssid = ss.getId();
var fileInDrive = DriveApp.getFileById(ssid);
var folderInDrive = fileInDrive.getParents().next().getId();
const urls = sheets.map((sheet) => {
const filename = `${sheet.getParent().getName()} ${sheet.getName()}.csv`;
const array = sheet.getDataRange().getValues();
const url = saveArrayToCSV_(array, filename, folderInDrive);
return { url, filename, folderInDrive };
});
console.log(JSON.stringify(urls, null, ' '));
}
/**
* @param {string[][]} array
* @param {string} filename
* @param {string} folderInDrive
* @returns {string} The file url
*/
function saveArrayToCSV_(array, filename, folderInDrive) {
const data = array.map((row) => row.join(';')).join('\n');
const url = DriveApp.getFolderById(folderInDrive)
.createFile(filename, data, 'text/csv')
.getDownloadUrl()
.replace('?e=download&gd=true', '');
return url;
}
function saveData2() {
const book = SpreadsheetApp.openById(
'1FUSSiDQoXyvKXfzYydoUUfcCGYq_TskpRiwfb28_1Z0'
);
const date = new Date();
const sheetTotal = book.getSheetByName('TOTAL');
const sheetGOOG = book.getSheetByName('NASDAQ:GOOG');
const valuesGOOG = sheetGOOG.getRange('C3:U3').getValues().map(row => [date, 'NASDAQ:GOOG', ...row]);
appendData_(sheetTotal, valuesGOOG);
const sheetDIS = book.getSheetByName('NYSE:DIS');
const valuesDIS = sheetDIS.getRange('C3:U3').getValues().map(row => [date, 'NYSE:DIS', ...row]);
appendData_(sheetTotal, valuesDIS);
console.info(`saveData was called successful`);
}
function appendData_(sheet, values) {
if (sheet.getLastRow() === sheet.getMaxRows())
sheet.appendRow([]);
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length)
.setValues(values);
}
saveData2
задан для дневного триггераfunction run() {
const sheet = SpreadsheetApp.getActiveSheet();
const base = sheet.getRange('I3:I');
const colFormula = sheet.getRange('J3');
draggDownFormulas_(base, colFormula);
const colFormula2 = sheet.getRange('K3');
draggDownFormulas_(base, colFormula2);
}
function runBulk() {
const formulasCells = ['J3', 'K3', 'M3'];
const sheet = SpreadsheetApp.getActiveSheet();
const base = sheet.getRange('I3:I');
formulasCells.forEach((cell) => {
const colFormula = sheet.getRange(cell);
draggDownFormulas_(base, colFormula);
});
}
const url =
'https://api.vk.com/method/users.get?&v=5.131&fields=first_name,last_name&access_token=' +
service.getAccessToken();
document
.getElementById('btnShowModalDialog')
.addEventListener('click', (e) => {
e.preventDefault();
google.script.run.showModalDialog();
});
range
.function runTamotsuSample() {
Tamotsu.initialize();
const agent = Tamotsu.Table.define({
sheetName: 'Sheet1',
idColumn: 'id'
});
agent.create({
date: new Date(),
number: 100,
string: 'Hi there'
})
}
=FILTER('Лист1'!F:F;ISNA(MATCH('Лист1'!F:F;'Лист1'!K:K;0)))
function REMOVESUBSTRDUPLICATES(string) {
var patt = /(.+)\1+/g;
var res = string;
var acc;
while (acc !== res) {
acc = res;
res = res.replace(/(.+)\1+/g, '$1');
}
return res;
}
try {
Drive.Files.trash(file.getId());
} catch (err) {
console.log(err.message, file.getName());
}
let files = DriveApp.getFolderById(tempFolderId)
.searchFiles('"me" in owners and title != "nothing"');
MMULT
результатов MMULT
=INDEX(
MMULT(
N(ARRAY_CONSTRAIN(C2:Z/(MMULT(TRANSPOSE(ROW(C2:Z)^0);N(C2:Z)));
MATCH(2;1/(B2:B<>"");1);
MATCH(2;1/(C1:1<>"");1)));
SEQUENCE(MATCH(2;1/(C1:1<>"");1);1)^0)/MATCH(2;1/(C1:1<>"");1)
)
MATCH(2;1/(B2:B<>"");1)
может быть избыточна в том смысле, что это поиск последнего действительного значения для обрезки. Если вы будете содержать последовательности заголовков событий и игроков без пробелов, то действительно следующее=INDEX(
MMULT(
N(ARRAY_CONSTRAIN(C2:Z/(MMULT(TRANSPOSE(ROW(C2:Z)^0);N(C2:Z)));
COUNTA(B2:B);
COUNTA(C1:1)));
SEQUENCE(COUNTA(C1:1);1)^0)/COUNTA(C1:1)
)
if(respons.getResponseCode()){
const data = JSON.parse(respons.getContentText());
console.log(data.info);
}
> JSON.parse('rewt{')
< VM240:1 Uncaught SyntaxError: Unexpected token r in JSON at position 0
at JSON.parse (<anonymous>)
at <anonymous>:1:6
function insertImageToCell() {
const image = SpreadsheetApp
.newCellImage()
.setSourceUrl('https://upload.wikimedia.org/wikipedia/commons/5/50/Smile_Image.png')
.build();
SpreadsheetApp.getActiveRange()
.setValue(image);
}
Функция вставки
/**
* Insert a single image to the cell. A1 is default
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
* @param {GoogleAppsScript.Base.Blob} blob
* @param {number} row
* @param {number} column
*/
function insertImageBlobToCell_(sheet, blob, row = 1, column = 1) {
const sheetName = sheet.getName();
const parentId = sheet.getParent().getId();
return DocsServiceApp.openBySpreadsheetId(parentId)
.getSheetByName(sheetName)
.insertImage([{ blob, range: { row, column } }]);
}
Пример вызова
/**
* Insert an image blob to the cell
*/
function userActionRun() {
const sheet = SpreadsheetApp.getActiveSheet();
const blob = UrlFetchApp.fetch(
'https://contributor.pw/img/post/sheets/sheets_mmult-some-uses-cases_01.png'
).getBlob();
insertImageBlobToCell_(sheet, blob);
}
Результат
Не забудьте добавить библиотекув свой проект. Пример манифеста в сниппете108j6x_ZX544wEhGkgddFYM6Ie09edDqXaFwnW3RVFQCLHw_mEueqUHTW