New answers tagged google-apps-script
0
Yes, this can be automated and there are multiple ways to do it.
One way is by using time-driven trigger from Google Apps Script to periodically scan the user sent emails. In order to don't have to involve the organization admin you should ask that each teammate create the time-driven trigger.
It's very likely that this can be done by using Google Apps ...
1
Use an if statement. The following example uses on edit event object, the range property from this object and the getSheet() method of Class Range and hte getName() method of Class Sheet:
function respondToOnEdite(e){
const sheet = e.range.getSheet();
if(sheet.getName() !== 'Tab1') returns;
// add here the rest of the code
}
The easier way to test ...
0
This script will paste the corresponding value into column B and H when the check box in column C is true and if we're in sheet 1.
Code
const onEdit = (e) => {
if(e.range.columnStart === 3) {
if(e.range.getSheet().getName() === 'Sheet1') {
const osB = e.range.offset(0,-1)
const osH = e.range.offset(0,5)
if(e.range.isChecked()) {
...
0
Normally it's just:
range.sort([{column: 3}, {column: 4}, {column: 5}, {column: 6}]);
So based on this wouldn't it be column: 3[0] , column 3[1] etc ? I dont know, I'm just kibitzing. Give it a go!
0
The script is not reading data from the spreadsheet, it's using the data from the following code lines:
const info = {
'Timestamp' : ['4/7/2021 16:30:41'],
'Location Code' : ['3456'],
'Upload Image' : ['https://drive.google.com/open?id=1mietxRRzJLiOzwU71dJSmHJp9H833nul'],
}
First you have to read the data from the spreadsheet.
const ...
0
Is there a way to prevent spreadsheet from updating the date to today's date in formula? [duplicate]
One way of solving this is to create Google Apps Script code that runs every time the spreadsheet changes (using the onEdit event - guide here).
The problem with your approach is that in general, all spreadsheet cells get recalculated when their source cells change, thus updating its contents.
0
You will have to loop over the sheets and use some criteria to decide whether to do the copy on a given sheet. Perhaps it would be easiest to use a regular expression to match sheet names, like this:
function copyToFirstFreeColumn() {
const sheetRegex = /^(Tab \d)/i;
const rangeA1 = 'A9:A12';
SpreadsheetApp.getActive().getSheets()
.forEach(sheet =&...
0
Try Array.filter like this:
const formRanges = formSheet.getRange('A29:J');
function Search() {
const searchKey = searchKeyRange.getDisplayValue().trim().toLowerCase();
const results = dataSheet.getDataRange().getDisplayValues()
.filter(row => searchKey === row[SEARCH_COL_IDX].trim().toLowerCase());
if (!results.length) {
ss.toast('Could ...
0
The sheet.setActiveCell(cell) method is undocumented and therefore unsupported, which may explain the observed behavior. It would probably be better to use cell.activate() or sheet.setCurrentCell(cell).
The two proposed solutions only iterate column A, so they will activate the last row that has visible content in column A but will ignore content in other ...
0
Google Apps Script runs on Google's servers rather than the user's computer, so things like browser extensions or anti-virus software are likely irrelevant.
The symptoms, as described, suggest that the issue is related to the account rather than to the computer used to access the script project.
If the account belongs to a managed Google Workspace domain, ...
0
This will copy the rows between the Start and End markers, not including the marker rows:
function copyToTop() {
const sourceSheet = SpreadsheetApp
.openById('1-y8PK1al2G0K90t_v6gKmbrM6W4BWpwMLaHa93qjWrs')
.getSheetByName('Sheet1');
const targetSheet = SpreadsheetApp
.openById('1-y8PK1al2G0K90t_v6gKmbrM6W4BWpwMLaHa93qjWrs')
....
0
The following code will find consecutive rows that fulfill some criteria and hide such rows fast by using just one sheet.hideRows() call per group.
/**
* Hides rows that have a given value in a given column.
*/
function hideRowsByValue() {
const columnToWatch = 7; // 1-indexed
const valueToWatch = 'hide';
const sheet = SpreadsheetApp.getActive()....
1
A Google Sheet can be embedded in a web site, but all changes by users are saved automatically and right away, and users see each other's changes in real time. Further, in order to enter numbers in cells, the users will need edit access to the spreadsheet. You can embed the spreadsheet so that the menu bar is not visible, but a determined user with edit ...
2
To permanently record a value in a log once a day, you will need a script. The script should read the value in cell D12 and use sheet.appendRow() or similar to append it to a log sheet together with a timestamp. Run the script through an installable trigger that executes once a day.
There is a complete example of how to do this in the ...
0
There are many ways to do this. One of them is to use Array.prototype.some(). I.E. instead of
if(mechName != "" && customerName != "" && vehicleName != "" && group != "" && jobType != "" && paid !="")
//If data is empty here if just wont send but I want it ...
0
You need to add a loop to iterate over all the messages of each thread.
for (var i=0; i < threads.length; i++) {
for (var j = 0; j < threads[i].getMessages().length; j++){
var message = threads[i].getMessages()[j];
// add here what you need to do with each message
}
}
1
Not the most elegant solution but I used the script cache as a mutex of sort to prevent too much executions in parallel. I'm sure some must execute at the same time but few enough to not be a problem for me.
// Entry point of the function
var cache = CacheService.getScriptCache();
var last = cache.get('running');
while (last != null) {
Utilities.sleep(500)...
0
Please try this:
function updateCells() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var spreadsheetSummary = ss.getSheetByName('Summary');
const range = spreadsheetSummary.getDataRange()
const values = range.getDisplayValues()
const transposeCheck = values[0].map((_, iCol) => values.map(row => row[iCol]).some(cell => cell))
var ...
0
Try this instead:
const updateCells = () => {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const spreadsheetSummary = ss.getSheetByName('Summary')
const range = spreadsheetSummary.getDataRange()
const values = range.getDisplayValues()
const transposeCheck = values[0].map((_, iCol) => values.map(row => row[iCol]).some(cell => cell))
...
0
This will do that:
Code
const columnsWithData = () => {
const sh = SpreadsheetApp.getActiveSheet()
const range = sh.getDataRange()
const values = range.getDisplayValues()
const transposeCheck = values[0].map((_, iCol) => values.map(row => row[iCol]).some(cell => cell))
const countBoolean = transposeCheck.filter(Boolean).length
...
0
Ok, I got it. There were sporadic blank emails being sent, and it seems that the email script was switching between active sheets, and basically looking in the wrong place. The second suggested link held the solution, with a little modification, as it was not a onFormSubmit trigger or a for loop in the code.
Thanks, @Tedinoz!
function sendEmails() {
var ss=...
1
Instead of calling trigFunc(e) directly from the on change trigger, add the following function to your project and set your trigger to call it:
function respondToOnChange(e){
if(e.changeType === 'INSERT_ROW') trigFunc(e);
}
Resources
https://developers.google.com/apps-script/guides/triggers/events
0
The for statement requires three parameters
one or more expressions for iteration variables initialization
a conditional expression
one or more expressions to change the iteration variables
Resources
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/for
Related
Conversion from Excel to Google Spreadsheet - sum across sheets
0
Solved
by using getRangList , assign any range list to a variable then use these variables anywhere
var ranges=sheet.getRangeList(['B5:I21' ,'B24:I36' , 'B39:I53' , 'B56:I68', 'B71:I90' , 'B93:I109' , 'B112:I126' , 'B129:I140' , 'B143:I157' , 'B160:I174' ,'B177:I188' , 'B191:I204' , 'B207:I220' , 'B223:I234' , 'B237:I248','B251:I262','B265:I275','B278:I287'...
1
solved
var ranges=sheet.getRangeList(['B5:I21' ,'B24:I36' , 'B39:I53' , 'B56:I68', 'B71:I90' , 'B93:I109' , 'B112:I126' , 'B129:I140' , 'B143:I157' , 'B160:I174' ,'B177:I188' , 'B191:I204' , 'B207:I220' , 'B223:I234' , 'B237:I248','B251:I262','B265:I275','B278:I287','B5:I21']);
ranges.clearcontent();
2
I agree with the above comments and the assessment that there is no formatting that can turn a string into a number. However, I have some thoughts for you.
First, this formula will be a little more flexible and succinct:
=IFERROR(REGEXEXTRACT(H6,"[\d\.]+")*10^IFERROR(VLOOKUP(REGEXEXTRACT(UPPER(H6),"K|M|B"),{"K",3;"M",6;...
0
sheet.range.offset(0, -1).getValue() is not using the appropriate syntax as the Class Sheet hasn't a range property. Use range.offset(0, -1).getValue()
Note 1:
Using "getActive" methods like getActiveSheet(), getActiveRange(), etc. under some circunstances might not work as expected. It's better to use the event object properties, i.e. instead of
...
Top 50 recent answers are included
