Tanaike’s code is a work of art, but I think it is based on an assumption that you would only run the script once. You’ve said that users will fill out a Google Form. You then manipulate this so that rows with identical columns will be transferred to one column. But ironically you then disassemble … Read more
There is Google Apps Script and Google App Engine which is quiet close to what you want. I don’t think Google Drive has that feature. 0 solved Can I execute code on Google Drive server infra?
I believe your goal is as follows. You want to retrieve the value of cell “B3” of “Config” sheet. You want to search the retrieved value from the column “A” of “Relação” sheet. When the value is found, you want to put the value of cell “D9” of “Config” sheet to the column “L” of … Read more
Google Apps script is based on javascript. VBA is based on Visual Basic. Hardcoding it is a possibility but you end up with some funky array and you’re then limited to VBA’s clunky array methods (or lack thereof). Function createChargeList() Dim var(5) var(0) = [{“No.”, “Name”, “Cooldown”, “Power”, “Energy Loss”, “Type”, “Damage Window Start”}] var(1) … Read more
How many times per day/month i can call url.fetch? (Какие есть ограничения по количеству url.fetch в день/месяц для google apps scripts?) [closed] solved How many times per day/month i can call url.fetch? (Какие есть ограничения по количеству url.fetch в день/месяц для google apps scripts?) [closed]
Use the following: An onEdit trigger to check when someone has updated a cell padStart() to add the leading zeros replace() the first 4 zeros with “CSC1” (since only the first occurrence will be replaced if passing a string instead of regular expression) setValue() to update the edited cell function onEdit(e) { if (e.range.columnStart == … Read more
You cannot search by duration with such granular detail; you can only provide a filter parameter that searches for “short” (under 4 minutes), “medium” (4 minutes – 20 minutes), or “long” (more than 20 minutes). The parameter is “videoDuration,” so your query function would look like this: var results = YouTube.Search.list(‘id,snippet’, { q: ‘dogs’, maxResults: … Read more
You can’t use getNamedRanges that way. You get all of the named ranges and then iterate over them. Then you can assign the rule. function dropOrderStatus() { var cell = SpreadsheetApp.getActive().getRange(‘B12’); var orderStatusRange = SpreadsheetApp.openById(“1sO_M9H7CrCevNrKCr0eimxb9lmY458NeyNHTf8RpS60”).getNamedRanges(); var namedRanges = []; for(var i = 0; i < orderStatusRange.length; i++) { namedRanges.push(orderStatusRange[i].getRange()); } //I have no idea what … Read more
You can use this sample code: function createDataValidation() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var s1 = ss.getSheetByName(“Sheet1”); var s2 = ss.getSheetByName(“Sheet2”); var s2_lastRow = s2.getLastRow(); //create data validation per row for (var row = 1; row <= s2_lastRow; row++){ //create an a1Notation to select a complete row sample: “A1:1”, “A2:2”, and so on. var a1Notation … Read more
The onOpen trigger in your script is only for adding the Custom Menu in your Sheet. The function will only get executed when the user selected an Item in the menu that is associated with the function. In your example, clicking Get place info will execute the COMBINED2 function. Also, executing the script only when … Read more
Introduction Creating a custom UI button with a growing Google Sheet can be a great way to automate certain tasks and make your workflow more efficient. However, it can be difficult to know how to properly call a function with such a button. In this article, we will discuss the steps necessary to properly call … Read more
Moving sheets/tabs of a color to end of list of tabs using Google Sheets API version 4 function moveSheetsOfAColorToEnd(color) { var color=color || ‘#ff0000’; if(color) { var ss=SpreadsheetApp.getActive(); var shts=ss.getSheets(); for(var i=0;i<shts.length;i++) { if(shts[i].getTabColor()==color) { Sheets.Spreadsheets.batchUpdate( { requests:[ { “updateSheetProperties”: { “properties”: { “sheetId”:shts[i].getSheetId(), “index”: shts.length }, “fields”: “index” } } ] }, ss.getId()); } … Read more
function rowstocolumn() { const ss = SpreadsheetApp.getActive(); const sh = ss.getSheetByName(‘Sheet2’); const osh = ss.getSheetByName(‘Sheet3’); const vs = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues(); let col = []; vs.forEach(r => { r.forEach(c => col.push([c])) }); osh.clearContents(); osh.getRange(1,1,col.length,col[0].length).setValues(col); } 2 solved Transpose N rows to one column in Google Sheets – Script Editor