Stream

Join this community to post or comment

Bruce Mcpherson
moderator

Problem/Coding Help  - 
 
I'm happy to share with you that my new video course .. Google apps script for beginners ... Has just been published by Oreilly media. 7.5 hours .. 84 videos.

http://www.infiniteskills.com/training/google-apps-script-for-beginners.html

Other of my titles published by Oreilly this year are
. Going gas
http://shop.oreilly.com/product/0636920045816.do

Google apps script for developers...
http://www.infiniteskills.com/training/google-apps-script-for-developers.html


#gde #googleappsscript #tutorial #video
7
1
123 DIGI • WIN Service GbR's profile photoHolger Hubbs's profile photo
2 comments
 
+123 DIGI • WIN Service GbR Having to search for it can be quite time consuming and frustrating; I lost interest in fiddling.
Add a comment...

garrin french

Problem/Coding Help  - 
 
If I used mail app to send an email and I wanted it to include both text/data from a cell in between this text, how would I do it. I can manage to send the text I want but can include the cell data from that row that I'm using. I created a var statement to reference the cell but not sure how to place that with script. Anyone help? 
1
garrin french's profile photoSpencer Easton's profile photo
5 comments
 
var cellValue = SpreadsheetApp.getActiveSheet().getRange("B2").getDisplayValue();
Add a comment...

Žygimantas Snarskis

Problem/Coding Help  - 
 
Question regarding building a student subject portfolio selector via google forms and google script! Hello everyone, I'm new to this group as well as google script so please bear with my inexperience. Anyways, for my final year of high school (IB diploma, if anyone's familiar with it) I have to develop a student profiling application as my end computer science project. By "profiling" app I mean an application that would allow the students to select what subjects they are going to study in 11th grade via google forms and the data from these forms would flow to a google sheets which would be accessed by the school's class administrator. The issue here is that I am not sure how to approach the project. The subjects can choose are limited by number and the total hour count, as well as subject category, and I am not too sure how to implement that into forms. The other concern of mine is that I am not sure how the google sheets that take data from forms would be constantly updated. Is there a way to make data from forms stream constantly to sheets? Any help will be more than appreciated as this is a pressing matter for me. To add to that, if this project of mine is successful, the school will actually implement it, so profiling wouldn't be done on paper anymore. Thank you in advance!
1
1
Michael Ellis's profile photoAlan Wells's profile photo
2 comments
 
There is no way for a Google Form to keep track of how many subjects a student has chosen in the Form, or what the total hours of those classes are, and then stop the user from inputting any more. You could have the student rank their preferences in the Form, then do all a calculations in the sheet. The only way to rank preferences would be in a Grid question type. If you want anything more capable or sophisticated than that, then you'll need to look at something other than Google Forms.
Add a comment...

Jacob Jan Tuinstra

Problem/Coding Help  - 
 

Hi Y'all,

I'm trying to set the active range of the second sheet and then return to the first, like so:

function onOpen() {
var ss = SpreadsheetApp.getActive();
ss.getSheets().some(function (d) {
if(d.getIndex() === 2) {
return d.setActiveRange(d.getRange("A" + (d.getDataRange().getHeight() + 1)));
}
});
ss.getSheetByName("Sheet1").setActiveSelection("A1");
}

Upon opening of the file, the second sheet has been re-worked but without showing the active range. If you then re-run the onOpen() it will perform as I want.

What am I missing here?

Regards Jacob Jan
1
123 DIGI • WIN Support's profile photoJacob Jan Tuinstra's profile photo
2 comments
 
+123 DIGI • WIN Support thanks....I'm getting sloppy / rusty
Add a comment...

1SG Jeffery W. Crane

Problem/Coding Help  - 
 
Team,

Hoping for a little help here... I am trying to add 1 to a variable in an Apps Script project as my for loop iterates through rows in a Google Sheet. I have tried ++variable and variable++ with no luck. Below is my current code:


var aMessages = 0;
var bMessages = 0;

Logger.log("a messages sent so far: " + aMessages);
Logger.log("b messages sent so far: " + bMessages);

if (recipientAddressRegex == "@amessages.com"){
++aMessages;
}else {
++bMessages;
}

Here is what exports in my Logger, repeatedly...:

[16-09-24 17:43:32:668 PDT] a messages sent so far: 0
[16-09-24 17:43:32:669 PDT] b messages sent so far: 0

1
1SG Jeffery W. Crane's profile photo
4 comments
 
Crap...  Moved the two variables outside of the for loop...  Problem solved...  Thanks for chiming in guys, the variable value was being reset to zero every time the loop cycled...
Add a comment...

Eric Schwartz

Problem/Coding Help  - 
 
Hi All,
A script adds records to a sheet which contains the source range for drop-down validation on cells in another sheet. After the new rows are inserted, values at one end or the other of the Look-Up sheet are not displayed in the cell validation drop down list.
Tried inserting rows at the top and middle of the Look-Up sheet, but no help there. Any suggestions on how to update the data validation criteria range?
Thx in advance.

1
Add a comment...

Mr Doll

Problem/Coding Help  - 
 
Sample Form:
https://docs.google.com/spreadsheets/d/1_IVM0y1RmHJCZ9bKT5O3MM3J-fCeZNsEGDRpuFFF-J8/edit?usp=sharing

Hi Everyone,

I am a teacher and I assign all of my students a 4 digit student code to help with grading online google forms. After I am finished grading I am left with two columns (their code and their grade). The problem is that I have 170 students and that on any given assignment a large percentage of students do not complete the form. This means that I have to take the time to go through and separate out the two columns into 10 columns (two columns (code,grade) for each of my classes) and I also have to skip a line for each missing assignment. (this way it is easier to upload to my schools grading software.

What I would like to do is to have script editor function that takes two columns of data and separates that out into the 5 classes (10 columns). The catch is that I would like to skip a line if a student code is missing (as in they didn't turn in the assignment).

The way that I think this could work is if I use a matching function where I match the grade input sheet to a reference sheet. Or perhaps a filter that uses fuzzy matching would work. I'm a bit lost, any help would be great. Thanks in advance for your time.


Drive
Grades SampleGrades Input Student codes, Grades 101a, 7 102a, 6 104a, 5 105a, 4 201a, 6 202a, 6 203a, 1 204A, 7 501a, 5 502A, 5 504a, 5 505a, 2 701a, 4 705a, 6 801a, 3 804a, 7 805a, 7 Grades Filter 1st Period Student Code, Grade, 2nd Period Student Code, Grade, 5th Period Student Code, ...
1
Add a comment...

Пашко Костенко

Problem/Coding Help  - 
 
Hi!
How to extract data from Google last answer form? It should be downloaded, than edit and at last must be added to the table using:
SpreadsheetApp.getActiveSheet().appendRow([something]);

1
Add a comment...

Christian Bauduin

Problem/Coding Help  - 
 
Hey I've been working on a spreadsheet for a while. My intention is to sell this sheet to specific seople.
My problem is now, that I need to give full rights to change the content in this sheet in order to make it usefull. This allows people to make copies and maybe share it with other people, which I don't want.

I found another sheet, that used a login button. The button triggeres a script, that checkes if the current user is on a specific list or not. This list was on a external webserver via php. How can I build something like that for my Sheet?

From what I understand I need:
1. It would be nice if the process from PayPal to drive is fully automated (Create an entry for that Person on a List on Wordpress or Drive after purchase).
2. The Button-Script checks this List and allows Access if this person is on that list.

Maybe someone can help me or point out an easier way to solve my problem.

Thanks

Chris :)
1
Alan Wells's profile photoSpencer Easton's profile photo
2 comments
 
The easiest way is to develop this as an add-on. You then have access to sell it in the Google Apps Marketplace. Our last community hangout discussed montization. https://www.youtube.com/watch?v=KY2ZmACxNGg
Add a comment...

France Paul Pastrana

Problem/Coding Help  - 
 
Hi Guys,

I'm using gmail apps script to display emails to HTML, how can i delete an email with automatically removing it in the list in HTML? because right now, when i click e delete button, the email is not removing from the list but in the background or in the server, it's already removed. You can only see the email is deleted if you refresh the HTML page.

I know i should use an AJAX or something with this but how can i start?

Thank you!
1
Add a comment...

Saad A

Problem/Coding Help  - 
 
I am using the Gmail Atom Feed to fetch my unread emails and display them in XML. I am looking at the XML that is generated and I cannot see the full message, there is part of the message under the "summary" tag but I want the full message for each of my unread email.

link : https://mail.google.com/mail/feed/atom

How can I get full email message using the above gmail atom feed? Are there any options available for it.

My end goal is to consume the page via my PowerShell script and do some stuff, but I need a service that will display full message of my email.

Please help,

Thank you


1
Add a comment...

Ross McMillan

Problem/Coding Help  - 
 
This must have been done by someone...

I have a heap of Google docs and sheets and it always worries me slightly that they aren't backed up locally. I would have thought that a script to iterate its way through the folder structure and download all the files as .docx, .xls, .ods, whatever would be reasonably easy (or create a ZIP file with the folder information stored as well as the filenames).

Is there any reason why this might not be possible? Does anyone know of anything out there?
2
Holger Hubbs's profile photo
 
One puzzle piece, to backup data: Google Takeout https://takeout.google.com/settings/takeout
Add a comment...

Siobhan Williams

Problem/Coding Help  - 
 
Help!

I was using a combination of Google Sheets, Google Apps Script, and Google Drive to make schedules and print them to an HTML file.

I would create the HTML file through Google Apps Script after grabbing data from the sheet.

I would then share the html file to the web and publish using googledrive.com/host. However, it has since been deprecated. How can I share HTML files in my Google Drive on the web?
1
Alan Wells's profile photo
 
I'm assuming that when you state "share", that you want the person who it is shared with to be able to see the rendered HTML. I think you can do that with Firebase hosting, which I believe has a free tier. If you are using Google Apps Script, you could use a stand alone Apps Script web app with HTML Service. You could probably use DriveApp to get a file as text content, and then create valid HTML with HTML Service. So, the web app would retrieve the file from drive as text or string content, then just use HTML Service to convert it to valid HTML, that you can then inject into your Web App.
Add a comment...

Joeri De Pauw

Problem/Coding Help  - 
 
Hi, I'm looking for a help on coding a custom function, based on IF, THEN ELSE logic.

I was thinking in line of :

function f_amt_IN(p_level, p_amt_out) {
IF (p_level)=3 THEN {return p_amt_out * 6}
ELSE IF (p_level)=2 THEN {return p_amt_out * 20}
ELSE IF (p_Level)=1 THEN {return p_amt_out * 160}
ELSE {return -1};
}


Basically I want to do a calculation on the parameter p_amt_out based on a rule for the parameter p_level, the function should output the calculated value, and if the level is outside of the range 1-3 it should result in -1

Obviously the code above is invalid, and I'm not familiar with G Apps Script, nor Java, but do have some coding experience on other stuff.

I know this can be done just inside the spreadsheet, however the idea is to do it in a function.

Thanks in advanced!

J.
1
Joeri De Pauw's profile photoSpencer Easton's profile photo
Add a comment...

Zibasedom Seblang

Problem/Coding Help  - 
 
Please help, I have this error with my google script :

Requête error : http://192.168.1.XX:XXXX/json.htm?type=command&param=udevice&idx=60&nvalue=0&svalue=maison (ligne 36, fichier "Code")

The part of google script is :

var headers = {
"Authorization" : "Basic " + Utilities.base64Encode(ZIBASE + ':' + TOKEN)
};
function traiterAujourdhui() {
var today = new Date();
envoi = UrlFetchApp.fetch("http://192.168.1.XX:XXXX/json.htm?type=command&param=udevice&idx=" + Z_VARIABLE_AUJOURDHUI + "&nvalue=0&svalue="+ getTypeOfADay(today) );
};

I do not understood the error !
If I put the link http://192.168.1.XX:XXXX/json.htm?type=command&param=udevice&idx=60&nvalue=0&svalue=maison in explorer it's work!
Why it doesn't work in the script?

tanks for your help

1
Zibasedom Seblang's profile photoSpencer Easton's profile photo
6 comments
 
I see a few issues. First what Benjamin pointed out is correct you are not passing your headers to the fetch method. It should look like UrlFetchApp.(url,options).
Secondly your headers object needs to be in a more comprehensive options object

var url = "http://192.168.1.XX:XXXX/json.htm?type=command&param=udevice&idx=" + Z_VARIABLE_AUJOURDHUI + "&nvalue=0&svalue="+ getTypeOfADay(today);

var options = {
method:"GET",
headers: {
"Authorization" : "Basic " + Utilities.base64Encode(ZIBASE + ':' + TOKEN)
}
}

envoi = UrlFetchApp.fetch(url, options)

Add a comment...

Deborah Brown

Problem/Coding Help  - 
 
I am new to coding, google scripts and add ons. I want to create or find a script that will lock my google sheets on a specific day each week (or specific date). I have a very basic understanding of writing code in java- just enough to know that I don't know what I need to accomplish this? Any help would be appreciated.
1
1
Riël Notermans's profile photoHari Shankar Das's profile photo
2 comments
 
Here is a link which shows how to remove editors. Similarly you can remove viewers. But you have to store the sharing permissions in user properties to revert it back on the next day.

http://stackoverflow.com/questions/32492180/how-to-remove-editors-from-googlesheets-using-script
Add a comment...

Riël Notermans
moderator

Problem/Coding Help  - 
 
Spam on Drive... anyone seen this before?
This stuff also ends up in your search results...
 
So... spammers are finding their way over Google Drive. That's alerting?

#spam #drive
2
Riël Notermans's profile photoHolger Hubbs's profile photo
9 comments
 
How to list and remove those shared documents... Including to permanently delete and maybe report the sender?
Add a comment...

Eric Ciesielski

Problem/Coding Help  - 
 
Hey Everyone,

I'm looking for some help writing a script that will export text from a note that is tied to a specific cell. Ideally, I would like this script to run monthly and capture the data as follows

- New sheet
- Column A (Header: Hospital) on new sheet would capture data from Column C (hospital) on example sheet
- Column B (Header: Affiliated Practice) on new sheet would capture the note entered in the corresponding cells
- Column C (Header: Group Size) on new sheet would capture number entered in Columns, M, R, W, AB, AG, AL, AQ, and AV


I've attached an example of what my sheet looks like as well as a script very similar to what I need. A coworker and I have worked with the attached script but haven't had it work 100% for us yet and it's not very efficient code


* Example sheet: https://docs.google.com/spreadsheets/d/1qrFxdsjW0-uDi7C5lug6LxhJlYEtgxboix4_RJHw5nU/edit?usp=sharing

* Example code: https://productforums.google.com/forum/#!topic/docs/0BbXNaICBa0

1
Add a comment...

Sam Danaher

Problem/Coding Help  - 
 
Hi all,

I was wondering if you could assist me in writing a script for a Google form. I am a teacher and I want to use a Google form to track student behaviour. I cannot, due to privacy laws in Australia, store student names in the cloud on the Google sheet.

I was wondering if I could get some help writing a script that would, once the form has been submitted, autoatically the name of the student is changed to the first 4 letters of their surname name and the first letter of their last name, this is an example

1. The student's name Bill Smith. I complete and submit the form with the student name Bill Smith
2. The name when viewed on the attached Google sheet identifies the student as SmitB
1
Faiz Ullah's profile photoAlan Wells's profile photo
3 comments
 
You will need to use JavaScript string methods to manipulate the name.

function getFirstLetterOfFirstName() {
var firstLetterOfFirstName, studentName;

studentName = "Bill Smith";

firstLetterOfFirstName = studentName.slice(0,1);
Logger.log("firstLetterOfFirstName: " + firstLetterOfFirstName);
};

Copy that code, paste it into the Apps Script code editor, then make sure that the function name is showing up in the drop down list in the toolbar, and click the "Run" button. The "Run" button is a triangle. Then from the View menu, choose "Logs" from the list. The print out should show the first letter of the first name. Do that, and post back if you got it to work.
Add a comment...