oshliaer / google-apps-script-snippets Goto Github PK
View Code? Open in Web Editor NEWGoogle Apps Script snippets
Home Page: https://apps-script-snippets.contributor.pw
License: The Unlicense
Google Apps Script snippets
Home Page: https://apps-script-snippets.contributor.pw
License: The Unlicense
function example() {
var data = [[1,2,'ывапвапывапэ'], [2,3,4]];
var csv = toCSV_(data);
var blob = newBlobWithCharset_(csv, 'text/csv', 'myfile.csv', 'Windows-1251');
DriveApp.createFile(blob);
}
function newBlobWithCharset_(data, contentType, name, charset){
return Utilities.newBlob('')
.setDataFromString(data, charset)
.setName(name)
.setContentType(contentType);
}
function toCSV_(arr){
return arr.reduce(function(p, c){
p += c.join(';') + '\n';
return p;
},'');
}
function makeTableHTML_(myArray) {
var result = "<table border=1>";
for(var i=0; i<myArray.length; i++) {
result += "<tr>";
for(var j=0; j<myArray[i].length; j++){
result += "<td>"+myArray[i][j]+"</td>";
}
result += "</tr>";
}
result += "</table>";
return result;
}
function sendMail(){
MailApp.sendEmail({
to: '[email protected]',
subject: "no subject",
htmlBody: makeTableHTML_([[1,2],["sad", "4"]])
});
}
/**
* @typedef {Object} HTTPEvent
* {@link https://developers.google.com/apps-script/guides/web Web Apps}
* @property {string} queryString The value of the query string portion of
* the URL, or null if no query string is specified.
* @property {object} parameter An object of key/value pairs that correspond to
* the request parameters. Only the first value is returned for parameters
* that have multiple values.
* @property {object} parameters An object similar to e.parameter, but with
* an array of values for each key.
* @property {string} [contextPath=''] Not used, always the empty string.
* @property {number} contentLength The length of the request body for
* POST requests, or -1 for GET requests.
* @property {HTTPEventPostData} postData postData
*/
/**
* @typedef {Object} HTTPEventPostData
* @property {number} length The same as e.contentLength
* @property {string} type The MIME type of the POST body
* @property {string} contents The content text of the POST body
* @property {string} postData Always the value "postData"
*/
/* exported doGet, doPost */
function doGet(e) {}
/**
*
* @param {HTTPEvent} e
*/
function doPost(e) {
console.log(e.postData.contents);
console.log(JSON.parse(e.postData.contents));
}
function example() {
// Make a POST request with a JSON payload.
var data = {
name: 'Bob Smith',
age: 35,
pets: ['fido', 'fluffy']
};
var options = {
method: 'post',
contentType: 'application/json',
// Convert the JavaScript object to a JSON string.
payload: JSON.stringify(data)
};
UrlFetchApp.fetch('https://script.google.com/macros/s/xxx/exec', options);
}
function unzipGmail(){
var message = GmailApp.getMessageById('15ea48eff1160e89');
var blobs = message.getAttachments()[0].copyBlob();
var data = Utilities.unzip(blobs);
data.forEach(function(blob){
DriveApp.createFile(blob);
});
}
I stumbled accross you autoid google sheet code on reddit today.
The link ot the GitHub page doesn't work and i can't see it in your list of projects. I was wondering if you are still supporting it at all?
I have an issue running it but not sure if you can still help or not?
Much appreciated.
AJ
function onOpen(e) {
try{
var __ = {
s: e.range.getSheet()
}
__.lr = __.s.getLastRow();
__.mr = __.s.getMaxRows();
__.s.getRange(__.lr == __.mr ? __.lr : __.lr + 1, 1).activate();
} catch (err){
console.error(err);
}
}
function example(){
var data = messageExtractor_('from:google.com is:inbox is:unread',
function(th, i, data){
data.push(['Тема', th.getFirstMessageSubject(), '', Utilities.formatString('https://mail.google.com/mail/u/0/#/%s', th.getId())]);
if(/ZDM/.test(th.getFirstMessageSubject())) return true;
}, function(msg, i, data){
if(msg.isUnread())
data.push(['Сообщение', '', msg.getPlainBody(), Utilities.formatString('https://mail.google.com/mail/u/0/#/%s', msg.getId())]);
}, []);
setValuesToBase_(data);
}
function messageExtractor_(q, threadCallback, messageCallback, data) {
threadCallback = getFn_(threadCallback);
messageCallback = getFn_(messageCallback);
var threads = GmailApp.search(q);
for (var i = 0; i < threads.length; i++) {
var __ = threadCallback(threads[i], i, data);
if(__ === true) continue;
var messages = threads[i].getMessages();
for(var j = 0; j < messages.length; j++){
messageCallback(messages[j], j, data);
}
}
return data;
}
function setValuesToBase_(values){
SpreadsheetApp.openById('1p9WFS0LXUX9VMojEjq8LktXzD3mXhEDHqIhPP177Bn0')
.getSheets()[0]
.clearContents()
.getRange(1, 1, values.length, values[0].length)
.setValues(values);
}
function getFn_(fn){
return typeof fn === 'function' ? fn: function(){};
}
hash_('test', 'MD2');
> dd34716876364a02d0195e2fb9ae2d1b
hash_('');
> d41d8cd98f00b204e9800998ecf8427e
hash_('test');
> 098f6bcd4621d373cade4e832627b4f6
/**
* Compute a hash string using the specified digest algorithm on the specified value.
* @param {String} value The specified value.
* @param {String} digestAlgorithm The name of Enum DigestAlgorithm: MD2, MD5, SHA_1, SHA_256, SHA_384, SHA_512
* @param {String} charset The name of Enum Charset: US_ASCII, UTF_8.
* @returns {String} The hash of value.
*/
function hash_(str, digestAlgorithm, charset) {
charset = charset || Utilities.Charset.UTF_8;
digestAlgorithm = digestAlgorithm || 'MD5';
var digest = Utilities.computeDigest(Utilities.DigestAlgorithm[digestAlgorithm], str, charset);
var __ = '';
for (i = 0; i < digest.length; i++) {
var byte = digest[i];
if (byte < 0) byte += 256;
var bStr = byte.toString(16);
if (bStr.length == 1) bStr = '0' + bStr;
__ += bStr;
}
return __;
}
/*
Testing by GasT https://github.com/zixia/gast
*/
function test(){
if ((typeof GasTap)==='undefined') { // GasT Initialization. (only if not initialized yet.)
var cs = CacheService.getScriptCache().get('gast');
if(!cs){
cs = UrlFetchApp.fetch('https://raw.githubusercontent.com/zixia/gast/master/src/gas-tap-lib.js').getContentText();
CacheService.getScriptCache().put('gast', cs, 21600);
}
eval(cs);
} // Class GasTap is ready for use now!
var test = new GasTap();
test('Md5', function (t) {
var i = hash_('');
t.equal(i, 'd41d8cd98f00b204e9800998ecf8427e', '"" is d41d8cd98f00b204e9800998ecf8427e')
});
test('Md5', function (t) {
var i = hash_('test', 'MD5');
t.equal(i, '098f6bcd4621d373cade4e832627b4f6', '"" is 098f6bcd4621d373cade4e832627b4f6')
});
test('Md2', function (t) {
var i = hash_('test', 'MD2');
t.equal(i, 'dd34716876364a02d0195e2fb9ae2d1b', '"" is dd34716876364a02d0195e2fb9ae2d1b')
});
test('SHA_1', function (t) {
var i = hash_('test', 'SHA_1');
t.equal(i, 'a94a8fe5ccb19ba61c4c0873d391e987982fbbd3', '"" is a94a8fe5ccb19ba61c4c0873d391e987982fbbd3')
});
test('SHA_256', function (t) {
var i = hash_('test', 'SHA_256');
t.equal(i, '9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08', '"" is 9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08')
});
test('SHA_384', function (t) {
var i = hash_('test', 'SHA_384');
t.equal(i, '768412320f7b0aa5812fce428dc4706b3cae50e02a64caa16a782249bfe8efc4b7ef1ccb126255d196047dfedf17a0a9', '"" is 768412320f7b0aa5812fce428dc4706b3cae50e02a64caa16a782249bfe8efc4b7ef1ccb126255d196047dfedf17a0a9')
});
test('SHA_512', function (t) {
var i = hash_('test', 'SHA_512');
t.equal(i, 'ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f50028a8ff', '"" is ee26b0dd4af7e749aa1a8ee3c10ae9923f618980772e473f8819a5d4940e0db27ac185f8a0e1d5f84f88bc887fd67b143732c304cc5fa9ad8e6f57f50028a8ff')
});
test.finish()
}
[
"Argentina",
"Armenia",
"Australia",
"Azerbaijan",
"Belarus",
"Bolivia",
"Brazil",
"Bulgaria",
"Canada (English)",
"Canada (French)",
"Chile",
"China",
"Colombia",
"Croatia",
"Czechia",
"Denmark",
"Ecuador",
"Egypt",
"Finland",
"France",
"Georgia",
"Germany",
"Greece",
"Hong Kong",
"Hungary",
"India",
"India (Bengali)",
"India (Gujarati)",
"India (Kannada)",
"India (Malayalam)",
"India (Marathi)",
"India (Panjabi)",
"India (Tamil)",
"India (Telugu)",
"Indonesia",
"Ireland",
"Israel",
"Italy",
"Japan",
"Kazakhstan",
"Latvia",
"Lithuania",
"Mexico",
"Mongolia",
"Myanmar (Burma)",
"Netherlands",
"Norway (Norwegian Bokmål)",
"Norway (Norwegian Nynorsk)",
"Paraguay",
"Philippines",
"Poland",
"Portugal",
"Romania",
"Russia",
"Serbia",
"Slovakia",
"Slovenia",
"South Korea",
"Spain",
"Spain (Catalan)",
"Sweden",
"Switzerland",
"Taiwan",
"Thailand",
"Turkey",
"Ukraine",
"United Kingdom",
"United Kingdom (Welsh)",
"United States",
"Uruguay",
"Venezuela",
"Vietnam"
]
I want to know ...
I need help ...
How can I ...
Hi
This is really useful. But I am struggling to get the bodyIndex number from my doc. I can give it different values and that seems to be making sense.
Any suggestions?
Peter
https://www.googleapis.com/auth/script.scriptapp
$> rsync -avrh --progress --exclude="node_modules" --exclude=".git" SRC DEST
To allow this add the next scope to appsscript.json
{
"oauthScopes": [
"https://www.googleapis.com/auth/script.container.ui"
]
}
function copyMultipleSpreadsheet2(){
var sheets = [];
sheets.push(SpreadsheetApp.openById('ssssQ').getSheetByName('Sheet1'));
sheets.push(SpreadsheetApp.openById('sssso').getSheetByName('Sheet1'));
var data = sheets.reduce(function(p, c){
var values = c.getRange('A2:D').getValues().filter(function(row){
return row[0]!=='';
});
p = p.concat(values);
return p;
}, []);
SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(1, 1, data.length, data[0].length).setValues(data);
}
``
Cookie handling in Google Apps Script - How to send cookies in header?
function example() {
Logger.log(getContacts());
}
// Some API
function getContacts() {
auth();
var headers = {
'Cookie': CacheService.getUserCache().get('auth')
};
var params = {
'headers': headers,
muteHttpExceptions: true
};
return UrlFetchApp.fetch( /* API URL */ , params);
}
// Auth flow
function auth() {
var cache = CacheService.getUserCache().get('auth');
if (!cache)
auth_();
}
function auth_() {
var params = {};
params.method = 'post';
params.payload = {
LOGIN: 'USER LOGIN',
PWD: 'USER PASSWORD'
};
var fetch = UrlFetchApp.fetch( /* API URL */ , params);
CacheService.getUserCache().put('auth', fetch.getAllHeaders()['Set-Cookie'], /* IF IT EXPIRES */ );
return fetch;
}
getPreFillEntriesMap_(id)
returns an ordered array of the Form's items with their entries
codes
[
{
"id": 1144844846,
"entry": "1854759972",
"titile": "Q1",
"type": "TEXT"
},
{
"id": 1458564606,
"entry": "1109661125",
"titile": "Q2",
"type": "TEXT"
},
{
"id": 216942465,
"entry": "1829112829",
"titile": "Q3",
"type": "MULTIPLE_CHOICE"
}
]
function run(){
Logger.log(JSON.stringify(
getPreFillEntriesMap_("1X9MBJDZYwrij8m_c7V4HbrT1cOyBu2OLRvDsnqS4Vdw"),
null,
" "
));
}
//https://docs.google.com/forms/d/e/1FAIpQLScdJPrXSFDyUZCi17JJDl1QxVSzi4b2mzpZdEJtLREActUMgQ/viewform?usp=pp_url&entry.1854759972=q1&entry.1109661125=q2
//https://docs.google.com/forms/d/e/1FAIpQLScdJPrXSFDyUZCi17JJDl1QxVSzi4b2mzpZdEJtLREActUMgQ/viewform
/*
createResponse()
Creates a new response to the form.
To answer a question item, create an ItemResponse from the item,
then attach it to this form response by calling FormResponse.withItemResponse(response).
To save the assembled response, call FormResponse.submit().
*/
function getPreFillEntriesMap_(id){
var form = FormApp.openById(id);
var items = form.getItems();
var newFormResponse = form.createResponse();
var itms = [];
for(var i = 0; i < items.length; i++){
var response = getDefaultItemResponse_(items[i]);
if(response){
newFormResponse.withItemResponse(response);
itms.push({
id: items[i].getId(),
entry: null,
titile: items[i].getTitle(),
type: "" + items[i].getType()
});
}
}
var ens = newFormResponse.toPrefilledUrl().split("&entry.").map(function(s){
return s.split("=")[0];
});
ens.shift();
return itms.map(function(r, i){
r.entry = this[i];
return r;
}, ens);
}
function getDefaultItemResponse_(item){
switch(item.getType()){
case FormApp.ItemType.TEXT:
return item.asTextItem().createResponse("1");
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
return item.asMultipleChoiceItem().createResponse(item.asMultipleChoiceItem().getChoices()[0].getValue());
break;
default:
return undefined;
}
}
Hi! I am using this code (which is awesome!) to insert a footnote in the middle of my automated document, but when using the code the footnote is always inserted in the end of the document.
How can I insert the footnote in a specific position?
function bulkWithSheets(){
var callback = removeSheetsToTheRight_;
SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(callback, {index: SpreadsheetApp.getActiveSheet().getIndex()});
}
function removeSheetsToTheRight_(sheet){
if(sheet.getIndex() > this.index)
sheet.getParent().deleteSheet(sheet);
}
function myFunction() {
var range = DocumentApp.getActiveDocument().getBody().findText("test.*");
Logger.log(range);
Logger.log(extractTextFromFindText_(range));
}
function extractTextFromFindText_(rangeElement){
return rangeElement ?
rangeElement.getElement().asText().getText()
.slice(rangeElement.getStartOffset(),
rangeElement.getEndOffsetInclusive() + 1) :
rangeElement;
}
function appendData(sheet, values, colOffset){
colOffset = colOffset || 1;
return sheet.getRange(sheet.getLastRow() + 1, colOffset, values.length, values[0].length).setValues(values);
}
function example(){
ll_(new Date(), {a:5, b:{c:'abc'}}, 6, 'def');
}
[17-10-19 23:21:42:458 MSK]
"2017-10-19T20:21:42.456Z"
{
"a": 5,
"b": {
"c": "abc"
}
}
6
The issue 'Target range and source range must be on the same spreadsheet' is present
В телеграм-боте неверное обозначение свойства. Должно быть не apikey
, а accesstoken
You do not have permission to call getEffectiveUser
"oauthScopes": [
"https://www.googleapis.com/auth/userinfo.email"
]
/**
* Returns sheet's name by gid
*
* @param {number} gid The gid of the sheet.
* @return {string} The name of the sheet.
* @customfunction
*/
function getSheetNameByGid(gid){
gid = +gid || 0;
var res_ = undefined;
var sheets_ = SpreadsheetApp.getActive().getSheets();
for(var i = sheets_.length; i--; ){
if(sheets_[i].getSheetId() === gid){
res_ = sheets_[i];
break;
}
}
return res_.getName();
}
I want to know ...
I need help ...
How can I ...
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.