Tag Info

New answers tagged


In the [File] » [Spreadsheet Settings ...] you can chose to have recalculations done "at change + every hour". But unfortunately this is only works when the spreadsheet is open = viewed in a browser. So it will not do a recalculation for you when the file is not open.


According to the documentation this option [noreply] is only possible for Google Apps accounts, not Gmail users


This is possible with a custom function or a script. Here's a custom function =sheets() which returns a column of sheet names, ordered as they are currently in the spreadsheet. You can use the output of this function as an input elsewhere, e.g., in =indirect, thus forming the master sheet according to the order of the other sheets. function sheets() { var ...


You can use MATCH to return the row number. For example if you're looking for the word "Help" in Column H, and it's on row 14: =MATCH("Help",H:H,0) will return 14.


As Rubén pointed out, custom functions cannot be used for this purpose. Sending an email requires authorization, which is not a part of custom function workflow. Quoting from documentation: custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data, ...


You are using the format sendEmail(recipient, subject, body, options) of the MailApp.sendEmail method. As the documentation says, if options include htmlBody, it will be used instead of the body parameter. This is why your first attempt ignored "message". Since you need to send an HTML message, its content must be formatted as HTML; in particular, line ...


The method DriveApp.getFilesByName returns a file iterator, not an individual file. This is why you can't just grab the contents of file. A more correct version is var file = DriveApp.getFilesByName('test123.pdf'); if (file.hasNext()) { MailApp.sendEmail(emailAddress, subject, message, { attachments: [file.next().getAs(MimeType.PDF)], name: ...


The method DriveApp.getFilesByName returns a file iterator, not an array. This is why file[0] is undefined. Use file.next() (after checking that file.hasNext() is true; if it's false then there is no file with that name).


You can use A:Z or A:AB or A:XX (with XX being the last column of your page):


I really like Evan's answer using Apps Script, and made a small improvement: adding support for regular expression delimiter matching. To the menuEntries in onOpen I added: menuEntries.push({ name: "Text to columns (regular expression separator)", functionName:"textToColumnsRegExp" }); And added the referenced function: function textToColumnsRegExp() ...


Managing expectations Scripts cannot intercept a user's click on column name. This action will always invoke built-in search. So, if you want users to sort by clicking a column, you need a separate column for sorting. With a separate column You don't need a complex formula with IFs to remove articles or other words: a regular expression can be used to ...


Database Software I think Google Sheets can do this but do consider whether to use proper database software (Google for "database software"). Those will handle tables of data with more/easier high-level control. They won't have cell formulas that can get inconsistent between rows, or cells accidentally added outside of a desired range. There are many ...

Top 50 recent answers are included