New answers tagged google-apps-script
0
Format your Tab Name referencing cell as Text. Or use =TEXT(Cell reference or Formula, "DDDD, MMMM, YYYY). In the example script above the cell would be A1. Hope this helps.
0
This code does exactly that: brings the list of all Gdrive files and folders (starting from a folder to specify at the top of the code) into a Google sheet (same original code as yours):
https://github.com/bluexm/snipets/blob/master/list%20Gdrive%20files%20and%20folders
/* Adapted from Code written by @hubgit https://gist.github.com/hubgit/3755293
Updated ...
1
Indeed, \w+ only searches for word characters, and "-" is not included. Your code, @Curious, didn't work for me, but I really liked the new width thing. I've changed \w+ for \S+, which searchs for non-whitespace characters, and mantained the NewWidth functionality. It is working fine for me.
function replaceLinksByImages() {
var searchPattern = "https://...
0
i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently.
Working directly on the actual google forms responses sheet is not advisable as google forms will overrite any preexisiting data and replace it with the form responses answers - be it text formulas or even cell formatting - theres ...
0
i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently.
The solution is simple
there are two parts to this
first you need to get the sheet to trigger & run a script code based on a cell value.
https://developers.google.com/apps-script/guides/triggers
you need to modify the code ...
0
i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently.
I use a simple workaround to bypass the google form overwriting formatting & deleting formulas & other data on the form responses answer sheet on each new entry.
With this solution i will show you - you get two benifits
...
0
i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently. I did something similar recently- auto populating & auto updating a google form with data values from the google sheet after a response has been submitted, here is the working live sample form:
1
The following block "doesn't work" because it's inside of the first if block
if(e.range.getA1Notation() === 'B5' && e.range.getSheet().getName()=='Temp1'){
if(e.value=='Base10') {
e.range.getSheet().showRows(10,2);
}
try
function onEdit(e){
if(e.range.getA1Notation() === 'B13' && e.range.getSheet().getName()=='Temp1'){
...
0
Check this link: [https://stackoverflow.com/questions/33647399/google-script-download-web-image-and-save-it-in-a-specific-drive-folder]
Once you get the file, you can rename the file as per your needs.
0
It's very likely that you spreadsheet has formulas that are returning an empty text or something similar. Review your spreadsheet carefully and adjust the formulas accordingly.
0
I just made it work by replacing dataSheet with sheet.
0
Found a similar solution, which kind of works:
https://gist.github.com/mesgarpour/07317e81e9ee2b3f1699
One more:
https://gist.github.com/hubgit/3755293
1
The code works fine for me on a simple test. I suggest you to add more log statements but instead of using Logger use console. i.e. You could log every file name and their Mime Type in order to find if there is an specific file that is causing the error.
Also beside logging only e.message include the e.stack
console.error('%s, %s', e.message, e.stack)
...
1
It's a late answer, but I posted it because I had the same problem, and the folowing unswer solved it for me :
To get the email address of the curent user you can use this function and it will return the email address saved in a variable :
function OnOpen(){
var emailName = Session.getActiveUser().getEmail();
var optionsHtml = emailName.toString()....
0
Google Apps Script doesn't include a way to call Google Document Editors built-in menu actions and I think that it could not be possible to do that from a dialog / sidebar created using the HTML Service because this elements are in an iframe nested inside the the page.
One alternative could be to use user scripts for extensions like Greasemonkey / ...
0
You want to update the maintenance log on Sheet A to remove the follow-up flag when a form response is received on Sheet B.
Install the following script
Update the sheet names as required
Create an Installable trigger "onformsubmit"
The script get the Unique ID from the "post followup" form and searches for that value in Column B of Sheet A. It uses the ...
2
I note that you have recently discovered =Sheet2!A1. This suggests that you are fairly new to spreadsheets and some allowance is justified in terms of the research that you might do and assist you.
There are several ways that you might achieve your goal, consider this answer as an example of one method.
As noted by @Rubén, what you are asking is possible ...
1
The syntax of the getRange is wrong. Replace
"L"&(nowts+1):"L"&(nowts+2)
(missing & and : should be inside a pair of quotes)
by
"L"&(nowts+1)&":L"&(nowts+2)
2
Google sheets now supports multiple hyperlinks in one cell. Select text fragment and press ctrl+K or use hyperlink icon after selecting text.
1
I was able to make it work using the code below. I used this answer as a reference: Google sheets loop search for string and clear adjacent cells
function deleteRows() {
var sheet = SpreadsheetApp.getActive().getSheetByName('OnGoing');
var lr=sheet.getLastRow()
var lc=sheet.getLastColumn()
var a = sheet.getRange(3, 1, lr, lc).getValues();
for (...
0
While cells formatting allow to apply some styling to text parts of the cell content, currently it's not possible to have more than one hyperlink.
A workaround is to use a script, more specifically to use the HTML Service from Google Apps Script to generate HTML from the cell content in a dialog or sidebar.
0
You want to protect cells containing content in the range E7:G150 on "sheet="CASH SALE" and range I6:L404 on sheet="HOPPER" at 10:00pm each night.
Locking cells in Google Sheets at a specific time (by @Punchlinernon on StackOverflow) provides a good basis for your solution. However your scenario is different because the range to be protected is extended ...
1
Instead of using A1:F1 use {A1,D1}. The final formula is
=COUNTBLANK({A1,D1})
{A1,D1} is an array having 1 row two columns.
NOTE if your spreadsheet uses a dot as decimal separartor instead of a comma use a slash
0
Your script can be resolved with a couple of simple changes in syntax
getValue(); - you should assign this result to a variable so that you can use the variable to the setValue() on the other spreadsheet
getValue Vs getValues - getValue returns a single value (the value of the top-left cell in the range); by comparison getValues returns the rectangular grid ...
0
You want to replace discrete values with replacement values.
The following answer assumes that the values are numeric; but would require only minor changes to adjust for string values.
Aspects to note
var range = sheet.getRange(11, 3, LR-10); - the range is defined according to the last row of data, as given by getLastRow(). This is a more efficient way to ...
0
This is a workaround that will dynamically update the range references when the range is updated (such as when a new row or column is inserted into it), but still input it as a string to myFunction:
=myFunction(CELL("address",A1)&":"&CELL("address",A2))
Courtesy of @Nathan Hanna at https://webapps.stackexchange.com/a/58179/22521
0
You are creating calendar events from a sheet="Test Weekend Coverage/Calendar Push - New" and you find that it is consistently re-processing existing entries instead of the last entry.
The calendar sheet is populated by a series of queries sourced from another sheet "Non-Grocery Join Sheet". "Non-Grocery Join Sheet" holds the raw entries for the calendar, ...
-1
use python with gam, and this script.
import os
import subprocess
from csv import writer
from csv import reader
path to googleidlist.csv
csvfile = 'c:\GAM\googleidlist.csv'
destination = 'c:\GAM\OUTPUT\' #Destination for downloaded documents
Open the input_file in read mode and output_file in write mode
with open(csvfile, 'r') as read_obj, \
...
0
It is not convenient/efficient for the output to appear in the same columns as the data. You can display the data in adjacent columns, or on a different sheet.
Name/Location/Floor
=transpose(split(arrayformula(textjoin(",",false,iferror(rept(A2:A&"."&B2:B&"."&C2:C&",",$D2:$D)))),","))
Port count (edit the column/row references to refer ...
0
As the error says, the results could not be expanded because J3 is not blank (empty). Also check that the cells below J3 are blank too.
Regarding the script you should have to change the following code line
var urlCol = 10;
Just replace 10 by the column number to be used for the response urls.
1
Replace
var file = DriveApp.getFilesByName(full_examname).next();
var subject = 'Sending emails from a Spreadsheet';
MailApp.sendEmail(emailAddress, subject, message, {attachments: file} );
by
var subject = 'Sending emails from a Spreadsheet';
var files = DriveApp.getFilesByName(full_examname);
if(files.hasNext()){
var file = files.next();
...
1
The accepted answers are far too complicated for running tallies. A much easier answer is:
=SUM(A$1:A1)
Substitute the A for the column you want. Substitute 1 for the row you want. Click the small box in the bottom right corner of the cell and drag it down to auto tally the cells you chose.
Note: I know when this was asked that my solution might not have ...
1
I had the same issue recently using the code you provided two weeks ago, so I eventually have rewritten it from scratch because I only needed a way to count the colored cells whose colors does not match the specified criterion color. This is what I've ended up with:
// based on https://stackoverflow.com/questions/14365436/count-the-cells-with-same-color-in-...
0
Although pnuts already described this technique in 2014, somehow it was unnoticed (I figured it out independently, and when I went to write this answer I noticed his answer as the lowest voted answer). So let me just explain the technique:
When Google Forms fills out a spreadsheet it empties the row it fills out, but it leaves intact all the other lines. ...
1
Try replacing one line of your code with this
if (r.getColumn() < 9 && ( ss.getName()=='WEST' || ss.getName()=='EAST' || ss.getName()=='NORTH' || ss.getName()=='SOUTH' ) ) {
It's a bit long, but it follows your current logic.
Top 50 recent answers are included
