Stream

Join this community to post or comment

Yan Vernet

Problem/Coding Help  - 
 
Does anyone know how to do ?
In my spreadsheet, as soon as a specific range A1:NJ4 in sheet1 is getting modified (text and/or content), I wanted to copy/paste with content to the the same range in Sheet2 and Sheet3 and Sheet4.
I was thinking to use onEdit() but I don't find a way to proceed....
Any help ?
Many thanks in advance
Yan
1
Hari Shankar Das's profile photoYan Vernet's profile photo
4 comments
 
yes that's true, sorry. I have mixed "my mind". the real things I wanted to write is "QUERY does not copy format of the cell source". (sorry for my confusion)
Add a comment...

Stefan Solbeck

Problem/Coding Help  - 
 
Hello can anyone help me with this script.
Do not get to run the script

function readContacts() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Master");
sheet.clear(); //clear the sheet

sheet.getRange("A2").setValue("FirstName");
sheet.getRange("B2").setValue("LastName");
sheet.getRange("C2").setValue("HomePhone");

var contacts = ContactsApp.getContacts()

for (var i in contacts) {

sheet.getRange(1*i+3, 1, 1, 1).setValue(contacts[i].getGivenName()); //A first name
sheet.getRange(1*i+3, 2, 1, 1).setValue(contacts[i].getFamilyName()); //B last name

// Error TypeError: Method "get Phone Number" from undefined can not be called.

var homePhones = contacts[i].getPhones(ContactsApp.Field.HOME_PHONE)
sheet.getRange(1*i+3, 3, 1, 1).setValue(homePhones[0].getPhoneNumber()); //C

}}
1
Alan Wells's profile photoRiël Notermans's profile photo
2 comments
 
There are no homePhones so the array is empty. Homephones[0] is undefined then.

First check if homephones.lenghth>0 and then do the homephones call.
Add a comment...

Jim Rowland

Problem/Coding Help  - 
 
How can I display a visible "sequential row count" when applying filters?

I have a Sheets tab where I need Column A to be a line-number count for each row in which there is data. This is easily doable with a function in A2:

=ARRAYFORMULA(if(isblank(B2:B),,ROW(A2:A)-1))

However, when I apply filters to other columns, the row number's "stick" to their row, giving me a line-number count of, for example, 1, 2, 5, 17, 31... etc. I need those five filtered rows to have column A update to now read: 1,2,3,4,5.

Several experts in the Google Sheets help forum have said, "not possible unless you use Apps Script". I have not found any solutions by googling, here in the G+ community, or in the Stack Overflow GAS help section.

I only have copy/paste experience with scripts - I have never successfully written my own, so I'm hoping that if this is possible to script, that someone is willing to do the work for me. (insert big, cheesy, pleading grin here)

Thanks for your time!
1
Jim Rowland's profile photoAlan Wells's profile photo
6 comments
 
So, the real issue is how long it's taking?
Add a comment...

Will West

Problem/Coding Help  - 
 
Hello :)

I have a GoogleSheet document that has multiple sheets. In each line of the the sheets, data is being fed in from a common sheet.

In each sheet, starting in line 5, column G is an email address.

When the value of "no" is selected in column A, I want an automatic email to be generated and sent to the email address in column G with data from: columns A, C, F, I, M, and N.

Is that something that is even possible to set up as a script?

Thanks!!!!

ww
1
Martin Molloy's profile photoWill West's profile photo
5 comments
 
THANK YOU Martin!!!!!! This is going to work perfectly!
Add a comment...

Harry P

Problem/Coding Help  - 
 
Hi guys -- i had a quick question. Is there a method I can call from GAS to programatically add a comment?

I see Drive.Comments.list to list all comments but nothing to add comments.

Also, when I say "Add a comment" I am referring to the bubble that pops-up when you select text. (see pic below)
1
Spencer Easton's profile photo
 
There is Drive.Comments.insert
https://developers.google.com/drive/v2/reference/comments/insert

I've got this to work with plain text files, but I've never been able to get this to work with google docs.
Add a comment...

Yves Sandyck

Problem/Coding Help  - 
 
Hello, someone on http://stackoverflow.com/ suggested to post my question here. I hope someone can help me with a little Google Apps Sheet Script.

Here's the situation. It is possible to download a Google Sheet to Excel. There are no settings, just export the whole sheet (so the complete workbook) to excel.

However, I am looking for a solution to ONLY export one sheet to Excel. Or even better, to only export a selection to excel.

Compare it how a pdf is made from Google Sheets. In the dialogue box you have the option to check Selection.

A nice button in the Google Sheets menu to export the selection/page to excel makes my question complete.

I hope to find a developer who can accomplish this. Just for the sake of creating or... mention me your fee.

All the best,

Yves
1
1
Hari Shankar Das's profile photoTakashi Kuramae's profile photo
7 comments
 
please try this sample.

function activeSheetDownload(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var downloadUrl = 'https://docs.google.com/spreadsheets/d/'+ss.getId()+'/export?format=xlsx&gid='+sheet.getSheetId();
var htmlApp = HtmlService
.createHtmlOutput('<A href="'+downloadUrl+'">download</A>')
.setTitle('ActiveSheet download')
.setWidth(150)
.setHeight(200);

SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);
}
Add a comment...

Michael Traub

Problem/Coding Help  - 
 
Dear All,
I have a problem accesing App.Form from a script within a Sheet.
Apperently the script is stopping after calling FormApp.openByUrl(); And some permission messages appeared in console.
I updated chrome, and set the permissions of both the form and the sheet to public, also tried openById with the edit and the view id but no success.
If anyone could help it would be realy great!
Thanks so far!


This is my Form:
https://docs.google.com/forms/d/1sjq2MGF0GuciQYT-32Xj4lV__v8_DNBmXPNepmfHYnI/edit

And this is my sheet:
https://docs.google.com/spreadsheets/d/1KWFRKZqO9kBWVS1kHs-V8yi6Rg-XhRdgWvXhImuWDG8/edit?usp=sharing

It contains a script:

function onOpen() {
updateForm();
}

function updateForm() {
var ss = SpreadsheetApp.getActive();
var names = ss.getSheetByName("egNames");

var startRow = 3;
var nameCol = 1;
var namesValues = names.getRange(startRow, nameCol, names.getLastRow() - startRow + 1).getValues();
SpreadsheetApp.getUi().alert(namesValues);

// get the Form
var form = FormApp.openByUrl('https://docs.google.com/forms/d/1sjq2MGF0GuciQYT-32Xj4lV__v8_DNBmXPNepmfHYnI/edit');
// code stops here because something is not right
SpreadsheetApp.getUi().alert("form");
var items = form.getId();
SpreadsheetApp.getUi().alert(items);
}

Console:
3987440900-ritz_waffle_i18n_ritzmaestro__de.js:76 Google Apps Script: Sie haben nicht die erforderliche Berechtigung zum Ausführen dieses Vorgangs.
(You don't have permission to run this..)
1
Michael Traub's profile photo
 
found the key to the problem: run the script from the scripteditor. there it will ask for permission. It didnt if the script was just run by "onOpen".
Edit: after working once, its broken again and not asking for permission even in script editor... I don't get it.
Add a comment...

Saai Murugan

Problem/Coding Help  - 
 
Dear All,

Help needed.

I am connecting to the SQL server we have in Dublin from google docs using the below code, but i am getting connection errors.

Can some one help me on this,

function SQLupdate(){

var address = '***';
var user = '***';
var userPwd = '***';
var db = '***';

var dbUrl = 'jdbc:mysql://' + address + '/' + db;

var conn = Jdbc.getConnection(dbUrl, user, userPwd);

conn.createStatement().execute("UPDATE `tb_documents` SET `XML_id`='456' WHERE `file_id`='1Lxwu-hinzIWSBnLh2EsvdzVkNRSGiXU_NCwe2qdDy3Q'");

conn.commit();
conn.close();
}

Thanks
Saai Murugan 
1
Saai Murugan's profile photo
6 comments
 
Dear Hari

Is it a paid service, i should buy for connect to my SQL server.

Thanks
Saai Murugan

Add a comment...

Chris Newport

Problem/Coding Help  - 
 
Hello, I'm learning Apps Script and having a problem passing a form object to the server from a Docs sidebar.

The test script is at http://goo.gl/Hnc0rT .

The sidebar will refresh okay without the <FORM> tag, but not when it's present (although the form object does get passed to the server). I'm not clear what I'm doing wrong, or have misunderstood.

Grateful for any advice / assistance. Thanks!
1
Eric Koleda's profile photoChris Newport's profile photo
4 comments
 
Ah, thank you! Adding the preventFormSubmit() function fixed the problem. I had seen that code already in the documentation but didn't understand the purpose or significance of it until now.
Add a comment...

venkatesh SQL

Problem/Coding Help  - 
 
Hi, I have a spreadsheet that captures data from a google form. I am storing all this information in my database. My users want near real-time data for their reporting needs. I tried onFormSubmit, onChange, onEdit none work as expected. Please guide me.

Thank you.
1
Riël Notermans's profile photoDarrin Brownlee's profile photo
6 comments
 
How is the data in your spreadsheet used for your users? Could you instead just use your MYSQL database instead of first storing the data in the spreadsheet and importing it into MYSQL? You could use HTML Service to create a custom web page interface for users to enter in the data that would save it directly to your MYSQL database. You could also create a HTML Service to create a custom web page interface to return the data to the users directly from MYSQL.
Add a comment...

Skip Allis

Problem/Coding Help  - 
 
Has anyone else had trouble converting Docs to pdf? I had a script that used doc.getAs() to create a pdf blob and then save it. It sill creates the pdf file that looks vaguely like the doc, but the formatting is messed up (specifically the right margin or indenting in a table - each subsequent row is indented less so that it starts outside of the table cell and can't be seen).

Anything I might be missing or something?
1
Steve Webster's profile photo
 
Perhaps it is related to a Google issue from last week where a sheet export or print to PDF was not rendering an image when =IMAGE was used. ???
Add a comment...

John Meyer

Problem/Coding Help  - 
 
99 little bugs in the code.
99 little bugs.
Take one down, patch it around.
127 little bugs in the code..
15
1
Alan Wells's profile photo
 
You are now a real programmer.  The honeymoon is over.
Add a comment...

Thomas Mills Hinkle

Problem/Coding Help  - 
 
Dumb question I can't seem to solve with google -- does anyone know how to properly escape spaces in an orgUnitPath? I'm trying to add users to the right orgUnit via script and I keep getting INVALID_OU -- our OrgUnit names have spaces in them... e.g. /Middle School/Class of 2024/

Alternatively, if I could feed the orgUnit ID to Users.insert in the AdminSDK, that could work too, but that doesn't seem to be one of the params you can pass in.
1
Thomas Mills Hinkle's profile photo
 
Solved issue -- no trailing slashes allowed on OU names. Spaces were a red herring.

Add a comment...

Srinivasulu Kalamula

Problem/Coding Help  - 
 
how to use vlookup inside javascript
1
Edouard Brasier's profile photoSrinivasulu Kalamula's profile photo
4 comments
Add a comment...

Marcelo Frias

Problem/Coding Help  - 
 
Hi, I'm looking for a script that will let me use multiple points/values for each answer of my questions. For example:

"What is this?

A) worth 1 point
B) worth 2 points"

Can anyone help? 
1
Marcelo Frias's profile photo
17 comments
 
+Bjorn Behrendt the problem is Google forms won't let me set points to the answers, just to the question. Anyway, +Alan Wells, flubaroo has what I need. Thx a lot. +Adam Lusk, thanks 4 ur help 2. 
Add a comment...

Ryan McKeen

Problem/Coding Help  - 
 
OK I am new to Google Forums. I love the idea of using forums to automatically input data for me. Currently operators fill out a piece of paper and than I half to enter the info into excel. I have set up a Forum, please see the bellow link.

Winnie Line Problem and Maintenance Report

What I want is for the individual inputting info into the forum to be able to go through each section and and input the data. But there are some things that happen regularly on the machine, so in section 1 of 5, I have a drop down for common occurrence. When they select, lets say "PC Crash" it will automatically go to the submit form page but by them selecting "PC Crash" it will also include standard pre-determined values in all the other cells without them having to answer all the questions. I'm not sure how this is done but I am sure it is possible. Any help would be greatly appreciated.

Thank You
Ryan
Drive
Winnie Line Problem and Maintenance ReportFields marked with an * are required
1
Alan Wells's profile photoRyan McKeen's profile photo
2 comments
 
I posted in google Forms and some said try here. I don't know if App Script can take one of the selections from Common Occurrence question and auto fill to the associated google Sheet. Thanks for taking the time to look and respond.
Add a comment...

Jason Elder

Problem/Coding Help  - 
 
I am back again, I am running this script to send and email report on one sheet. how do I can I changed the script to include 2 sheets? I would like it to pick sheets 2 and 3.

function emailAsExcelBillingAugust() {

var mailTo = 'test@gmail.com',
subject = 'Billing and Proactives Aug 2016',
body = 'Report of Billing and Proactives Aug 2016',
spreadsheetSourceURL = "URL of document",
sheetNumToSend = 2,
attachmentName = "Billing_and_Proactives_Aug_2016.xlsx";


var spreadsheetSource = SpreadsheetApp.openByUrl( spreadsheetSourceURL );
var sheetSource = spreadsheetSource.getSheets()[ sheetNumToSend ];
var displayValues = sheetSource.getDataRange().getDisplayValues();
var spreadsheetToExport = SpreadsheetApp.create( Utilities.formatString( "Copy of %s (%s)", spreadsheetSource.getName(), new Date().toJSON() ));
sheetSource.copyTo( spreadsheetToExport );
spreadsheetToExport
.getSheets()[ 1 ]
.setName( sheetSource.getName() )
.getDataRange().setValues( displayValues );
spreadsheetToExport.deleteSheet( spreadsheetToExport.getSheets()[ 0 ] );
SpreadsheetApp.flush();

var spreadsheetExportURL = Drive.Files.get( spreadsheetToExport.getId() ).exportLinks[ MimeType.MICROSOFT_EXCEL ];
var response = UrlFetchApp.fetch( spreadsheetExportURL, {
headers: {
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()
}
});
MailApp.sendEmail( mailTo, subject, body , {
attachments: [ response.getBlob().setName( attachmentName ) ]
});
DriveApp.getFileById( spreadsheetToExport.getId() ).setTrashed( true );
}
1
Add a comment...

Saai Murugan

Problem/Coding Help  - 
 
Dear All

One more question, i have now developed some scripts and now i want them to be available for all the documents in my Drive.

My script has a menu which will execute some cleanup on the document, so i want this menu to be visible when i open any of the Documents from my Drive using my account.

How should i do that.

Thanks
Saai Muurgan
2
Eric L.'s profile photoSaai Murugan's profile photo
4 comments
 
Ok, thanks for the confirmation.
Add a comment...

Matt Ballek

Problem/Coding Help  - 
 
I'm trying to create a timestamp function that won't automatically update every time I open the spreadsheet. Any ideas?

Here is the very basic function I'm using:

function timestamp() {
return new Date();
}
1
Hari Shankar Das's profile photoSpencer Easton's profile photo
3 comments
 
I use 'CTRL+;' if I need a time stamp.
Add a comment...

Ninh Huỳnh Văn

Problem/Coding Help  - 
 
Hi everybody,

I have some difficulty, expect people to help. Thanks a lot.

I want to transfer cell formulas to calculate F2 to G2.

Link: https://docs.google.com/spreadsheets/d/1ukuf9R2BsbVY_SSJ78QTn9JOZ747psBvLiD4uuY_Lu4/edit#gid=0
Drive
DEMOTrang tính1 Formula, Result 23, D2+ D3, 68 45
1
Emil Dąbrowski's profile photoNinh Huỳnh Văn's profile photo
12 comments
 
Hi Emil Dąbrowski, you can adjust the formula in one sheet (Formula) taking it? Thank you!
https://docs.google.com/spreadsheets/d/1ukuf9R2BsbVY_SSJ78QTn9JOZ747psBvLiD4uuY_Lu4/edit#gid=0
Add a comment...