goris is a CLI tool to search for images with Google Reverse Image Search.
Today, it was updated to v2.0.0. Please check it out. https://github.com/tanaikech/goris
v2.0.0 (April 23, 2020)
This is a sample flow for updating a file with the resumable upload using Drive API.
In this answer, as a sample situation, it supposes that a text file in Google Drive is updated by the resumable upload with the multiple chunks. And as the method for requesting, I use the curl command.
I prepared 2 files for 2 chunks. As the test situation, the 2 chunks of 262,144 bytes and 37,856 bytes are uploaded. So total upload size is 300,000 bytes. Those filenames are data1.txt and data2.txt, respectively.
When V8 is enabled, Array.apply has the limitation for the number of elements. When it is over the limitation, an error like RangeError: Maximum call stack size exceeded occurs, while the issue didn’t occur when V8 is disabled. In this case, this issue occurs at both Google Apps Script and Javascript. So please be careful this.
For example, when Array.prototype.push.apply is used for combining the arrays because the process cost of Array.prototype.push.apply is lowest of 3 patterns of Array.prototype.push.apply, Spread syntax and concat, please be careful above issue. Ref
v1.0.1 (April 13, 2020)
You can check this at https://github.com/tanaikech/FetchApp.
This sample script is for expanding a1Notations using Google Apps Script. This was updated from this sample script.
function expandA1Notations_(a1Notations, maxRow, maxColumn) {
maxRow = maxRow || "1000";
maxColumn = maxColumn || "Z";
// Ref: https://stackoverflow.com/a/21231012/7108653
const columnToLetter = column => {
let temp,
letter = "";
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
};
const letterToColumn = letter => {
let column = 0,
length = letter.length;
for (let i = 0; i < length; i++) {
column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column;
};
const reg1 = new RegExp("^([A-Z]+)([0-9]+)$");
const reg2 = new RegExp("^([A-Z]+)$");
const reg3 = new RegExp("^([0-9]+)$");
return a1Notations.map(e => {
const a1 = e.split("!");
const r = a1.length > 1 ? a1[1] : a1[0];
const [r1, r2] = r.split(":");
if (!r2) return [r1];
let rr;
if (reg1.test(r1) && reg1.test(r2)) {
rr = [r1.toUpperCase().match(reg1), r2.toUpperCase().match(reg1)];
} else if (reg2.test(r1) && reg2.test(r2)) {
rr = [
[null, r1, 1],
[null, r2, maxRow]
];
} else if (reg1.test(r1) && reg2.test(r2)) {
rr = [r1.toUpperCase().match(reg1), [null, r2, maxRow]];
} else if (reg2.test(r1) && reg1.test(r2)) {
rr = [[null, r1, maxRow], r2.toUpperCase().match(reg1)];
} else if (reg3.test(r1) && reg3.test(r2)) {
rr =
Number(r1) > Number(r2)
? [
[null, "A", r2],
[null, maxColumn, r1]
]
: [
[null, "A", r1],
[null, maxColumn, r2]
];
} else if (reg1.test(r1) && reg3.test(r2)) {
rr = [r1.toUpperCase().match(reg1), [null, maxColumn, r2]];
} else if (reg3.test(r1) && reg1.test(r2)) {
let temp = r2.toUpperCase().match(reg1);
rr =
Number(temp[2]) > Number(r1)
? [
[null, temp[1], r1],
[null, maxColumn, temp[2]]
]
: [temp, [null, maxColumn, r1]];
} else {
throw new Error(`Wrong a1Notation: ${r}`);
}
const obj = {
startRowIndex: Number(rr[0][2]),
endRowIndex: rr.length == 1 ? Number(rr[0][2]) + 1 : Number(rr[1][2]) + 1,
startColumnIndex: letterToColumn(rr[0][1]),
endColumnIndex:
rr.length == 1
? letterToColumn(rr[0][1]) + 1
: letterToColumn(rr[1][1]) + 1
};
let temp = [];
for (let i = obj.startRowIndex; i < obj.endRowIndex; i++) {
for (let j = obj.startColumnIndex; j < obj.endColumnIndex; j++) {
temp.push(columnToLetter(j) + i);
}
}
return temp;
});
}
// When you use this script, please run main().
function main() {
const a1Notations = ["A1:E3", "B10:W13", "EZ5:FA8", "AAA1:AAB3"];
const res = expandA1Notations_(a1Notations);
console.log(res);
}
[
["A1","B1","C1","D1","E1","A2","B2","C2","D2","E2","A3","B3","C3","D3","E3"],
["B10","C10","D10","E10","F10","G10","H10","I10","J10","K10","L10","M10","N10","O10","P10","Q10","R10","S10","T10","U10","V10","W10","B11","C11","D11","E11","F11","G11","H11","I11","J11","K11","L11","M11","N11","O11","P11","Q11","R11","S11","T11","U11","V11","W11","B12","C12","D12","E12","F12","G12","H12","I12","J12","K12","L12","M12","N12","O12","P12","Q12","R12","S12","T12","U12","V12","W12","B13","C13","D13","E13","F13","G13","H13","I13","J13","K13","L13","M13","N13","O13","P13","Q13","R13","S13","T13","U13","V13","W13"],
["EZ5","FA5","EZ6","FA6","EZ7","FA7","EZ8","FA8"],
["AAA1","AAB1","AAA2","AAB2","AAA3","AAB3"]
]
When this script is used, the duplicated cells can be retrieved as follows.
The drawing objects on Google Spreadsheet got to be able to be managed by the update at April 2, 2020. Ref
By this update, we can assign the function to the drawing objects and remove the drawing objects. The following sample is for removing the drawing objects on the active sheet. Ref
SpreadsheetApp.getActiveSheet()
.getDrawings()
.forEach(e => e.remove());
This is a sample script for retrieving the overwrapped cells between 2 ranges on Google Spreadsheet using Google Apps Script. Please use this with enabling V8.
const getOverwrappedRanges_ = (rangeList1, rangeList2) => {
if (
rangeList1.toString() != "RangeList" ||
rangeList2.toString() != "RangeList"
) {
throw new Error("Input RangeList object.");
}
// Ref: https://stackoverflow.com/a/21231012/7108653
const columnToLetter = column => {
let temp,
letter = "";
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
};
// Expand range1.
const ar = rangeList1.getRanges().reduce((ar, r) => {
const startRow1 = r.getRow();
const endRow1 = startRow1 + r.getNumRows();
const startColumn1 = r.getColumn();
const endColumn1 = startColumn1 + r.getNumColumns();
for (let j = startRow1; j < endRow1; j++) {
for (let k = startColumn1; k < endColumn1; k++) {
ar.push(columnToLetter(k) + j);
}
}
return ar;
}, []);
// Expand range2.
const map = rangeList2.getRanges().reduce((m, r) => {
const startRow2 = r.getRow();
const endRow2 = startRow2 + r.getNumRows();
const startColumn2 = r.getColumn();
const endColumn2 = startColumn2 + r.getNumColumns();
for (let j = startRow2; j < endRow2; j++) {
for (let k = startColumn2; k < endColumn2; k++) {
m.set(columnToLetter(k) + j, null);
}
}
return m;
}, new Map());
return ar.filter(e => map.has(e));
};
const main = () => {
const range1 = ["B3:C7", "D6:E9"]; // Please input range1 as a1Notation.
const range2 = ["A2:B3", "C7:D10"]; // Please input range2 as a1Notation.
const sheet = SpreadsheetApp.getActiveSheet();
const res = getOverwrappedRanges_(
sheet.getRangeList(range1),
sheet.getRangeList(range2)
);
console.log(res); // <--- ["B3","C7","D7","D8","D9"]
};
In this sample script, from the ranges of "B3:C7", "D6:E9" and "A2:B3", "C7:D10", the overwrapped cells are returned. In this case, ["B3","C7","D7","D8","D9"] is returned.
Kanshi Tanaike
V8 engine got to be able to be used at Google Apps Script. By this, I have reported about the process costs with and without using V8. Ref It is considered that knowing the process costs for various methods will be useful for creating the applications with Google Apps Script. Here, I would like to introduce the process costs of each situations under V8. The situations which measured the cost are as follows.
This is a sample script for retrieving the files and folders which have no parents in own Google Drive.
When you use this script, please enable Drive API at Advanced Google services.
const myFunction = () => {
const token = ScriptApp.getOAuthToken();
const fields = decodeURIComponent(
"nextPageToken,files(name,id,mimeType,parents)"
);
const q = decodeURIComponent("'me' in owners and trashed = false");
let files = [];
let pageToken = "";
do {
const res = UrlFetchApp.fetch(
`https://www.googleapis.com/drive/v3/files?pageSize=1000&fields=${fields}&q=${q}&pageToken=${pageToken}`,
{ headers: { authorization: `Bearer ${token}` } }
);
const obj = JSON.parse(res);
Array.prototype.push.apply(files, obj.files);
pageToken = obj.nextPageToken;
} while (pageToken);
const result = files.filter(({ parents }) => !parents);
console.log(result);
};
When you run the script, the files and folders which have no parents in own Google Drive are retrieved.
Web Application for searching Google Apps Script Library from Database was completed. So I published it as v1.0.0. Please check Google Apps Script Library Database.