Stream

Join this community to post or comment

Eric L.

Problem/Coding Help  - 
 
What's the best way to insert lots of data into a spreadsheet? Right now I'm putting my data into a two dimensional javascript array, and then using
range.setValues(dataArray);

This operation takes anywhere from 8-30 seconds. Is there a better way to do this?
1
Add a comment...

Arul F

Problem/Coding Help  - 
 
how to google site api using Authorizing requests with OAuth 2.0
here is the reference link
https://developers.google.com/google-apps/sites/
please help me Authorizing sample code.
1
Add a comment...

Bjorn Bogers

Problem/Coding Help  - 
 
I am trying to find out what the fastest way is to get some data from a row where a value matches my query, I am currently using this code:

var spr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('CustomerList'); 
  var klantNummer = array1[0].toString();
  var emailgebruiker = Session.getActiveUser().getEmail();      
  var data = spr.getDataRange().getValues(); 
  for(n=0;n<data.length;++n){ 
    if(data[n][0].toString() == klantNummer){ //.match(klantNummer)
      data[n][21] = emailgebruiker

   var KlantNR = data[n][0];
   var Email = data[n][3];
   var Stad = data[n][12];          
   var Taalschool = firstToUpperCase( data[n][13].toString() );    
   var Cursus = firstToUpperCase( data[n][14].toString() );      
   var Weken = data[n][15];
   var Accommodatie1 = data[n][17];
   var TypeAccommodatie = data[n][18];
   var TypeKamer = data[n][19];
   var VertrekDatum1 = data[n][20];

    };
  }
  spr.getRange(1,1,data.length,data[0].length).setValues(data); 

It works fine when I use it in a small sheet but when the sheet gets bigger it starts to become slow, is there a better way to do this?
 ·  Translate
1
1
Bjorn Bogers's profile photoSpencer Easton's profile photoJoris Steurs's profile photo
3 comments
 
I was able to search 5000 rows in about ~.3-.6 seconds
Add a comment...

Arul F

Problem/Coding Help  - 
 
function googleOAuth(name, scope) {
  var oAuthConfig = UrlFetchApp.addOAuthService(name);
  oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oAuthConfig.setConsumerKey("anonymous");
  oAuthConfig.setConsumerSecret("anonymous");
  return {oAuthServiceName:name, oAuthUseToken:"always",muteHttpExceptions:true};
}


I think addOAuthService is deprecated to April 20 2015. how to change alternative code please help me
1
Arul F's profile photoEric Koleda's profile photo
3 comments
 
We've published a migration guide here: https://developers.google.com/apps-script/migration/oauth-config
Add a comment...

Pako LordPakus

Problem/Coding Help  - 
 
i need to know how i can detect the creation or destruction of a sheet so i can refresh information  on my script.

Now i can detect when is modified the information of a sheet, but not when this is created or deleted.

Thank to all.
1
Spencer Easton's profile photoFaustino Rodriguez's profile photo
2 comments
 
+Spencer Easton great hint, thanks
Add a comment...

Ashley Moat

Problem/Coding Help  - 
 
Help needed!

I have added a menu button on my spreadsheet that when pressed, the active cell and its row of data will move to my other sheet. 

I cannot get this to work. Please can someone help me. 

Many thanks
3
1
Ashley Moat's profile photozoome i.am.'s profile photoJoris Steurs's profile photo
10 comments
 
sheet.deleteRow(activeRowNumber)
Add a comment...

Nuno Nogueira

Problem/Coding Help  - 
 
I have the following code in a sidebar of a spreadheet:
<?!= PropertiesService.getUserProperties().getProperty('KEY') ?>

Which returns the value of the KEY property.

But, the following code returns "Syntax Error:"

<?!= var x=PropertiesService.getUserProperties().getProperty('KEY') ?>

Why?
1
Spencer Easton's profile photoNuno Nogueira's profile photo
2 comments
 
That's it, thank you!
Add a comment...

Charlie Greenwood

Problem/Coding Help  - 
 
I'm trying to upload a file via form in a very similar way to the HTML service tutorial, however whenever I include a file in the form, the code never fires the function in the .gs file. If I create a similar form without a file upload it I can send the form parameters to the .gs file fine.  
http://goo.gl/ike0HO
2
Spencer Easton's profile photoCharlie Greenwood's profile photo
3 comments
 
Thanks for the quick responses guys! Spencer I'll try your code today!
Add a comment...

Johan Deknudt

Problem/Coding Help  - 
 
Hello,

Is it possible for me to do this?

if (item.getType() == FormApp.ItemType.MULTIPLE_CHOICE) { 
    var choices = item.asMultipleChoiceItem().getChoices();};
if (item.getType() == FormApp.ItemType.CHECKBOX) { 
    var choices = item.asCheckboxItem().getChoices();};
if (item.getType() == FormApp.ItemType.LIST) { 
    var choices = item.asListItem().getChoices();};

for (var j = 0; j < choices.length; j++) {
        var choice = choices[j].getValue().toString();
//Code
}

It gives an error on the bold line.
1
Johan Deknudt's profile photoRiël Notermans's profile photo
6 comments
 
add a

default:
var choices = []
break;


Add a comment...

Lindsay Robinson

Problem/Coding Help  - 
 
I need help with adding to an array. I have a google apps script that sends an email with a number of attachments. It sends like   MailApp.sendEmail(email, subject, body, {bcc: 'email@address.com', name: 'From Name', replyTo: 'email@address.com', htmlBody: body, attachments: [pdf1, pdf2, pdf3, pdf4, pdf5]});

I wish to however add any number of attachments by pushing another PDF on the end of an array. Can anyone suggest best way to do it? Something like:-

var attachmentarray=[];
attachmentarray.add(pdf1);
attachmentarray.add(pdf2);
attachmentarray.add(pdf3);
MailApp.sendEmail(email, subject, body, {bcc: 'email@address.com', name: 'From Name', replyTo: 'email@address.com', htmlBody: body, attachments: attachmentarray});


Appreciate any help!

Thanks
1
Andrew Roberts's profile photoLindsay Robinson's profile photo
4 comments
 
Should be PDFs no images at all
Add a comment...
 
Hey scripters! I'd like to capture User Agent with each response to a Google Form. Is this possible through Google Apps Script? Could someone give me some pointers? Thanks!
1
Add a comment...

Aaron Rackley

Problem/Coding Help  - 
 
Hi all. I am looking for help. I am trying to move across from one spreadsheet to another when a status is changed to Done. However this is not working on the for responses sheet. Does anyone have any ideas.

Code Below. 
function onEdit(e){
  
  /*
   When a user updates a status to done the ticket gets moved to another sheet
  */
  var sheetNameToWatch = "Form responses 1"; // The sheet to watch for changes
  var columnNumberToWatch = 1; // The column where the change happens on the above sheet
  var valueToWatch = "Done"; // What is the text you are looking for
  var sheetNameToMoveTheRowTo = "Done"; // The Sheet name for where the row gets moved to.
  
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet(); // Get the active sheet
  var range = sheet.getActiveCell(); // Get the current cell that has just been updated
  
  // Check that you are on the correct sheet and column. 
  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo); // get a reference for the target sheet
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); // get a reference for the target row
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange); // copy the row from current sheet to the new sheet
    sheet.deleteRow(range.getRow()); // Delete the row from the old sheet
    //sheet.hideRow(range.getRow());
  }
  
}
1
Aner Ben-Artzi's profile photoMichael Ellis's profile photo
2 comments
 
I believe +Aner Ben-Artzi is correct.  The Active cell might change between the time the OnEdit is triggered and the line of code is reached.  It's dicey to depend on timings like that from Google scripts.  The "e" structure should give you with certainty which cell changed and it's value.
Add a comment...

Edward Ulle

Problem/Coding Help  - 
 
How do you reference images to insert in a custom dialog for spreadsheet app using HTMLService?
1
Edward Ulle's profile photoEric Koleda's profile photo
3 comments
 
Apps Script doesn't provide asset hosting itself, but Google Drive does let you publish files to the web:

https://support.google.com/drive/answer/2881970?hl=en

You can then use those URLs in your sidebar or dialog.
Add a comment...

Ion Zubizarreta

Problem/Coding Help  - 
 
Hi everyone. I am trying to create a script in Google Forms. The script will execute with onSubmit trigger. My idea is to record the responses in a different Spreadsheet not in Responses Spreadsheet.

But the problem is when I try to read live responses, I mean, the responses that I have answered before on submit.

It's a problem because first, when I execute getResponses() method, it returns all the responses that the users has given and I only need the last one.

Then, if I reset all the responses (with the purposes of only read the last one) the first time when I execute getResponses() it always returns me null. So, Can someone help me and explain me, how can I full fit a Form and record the answers in the Spreadsheet that I want??
2
Stephen Gale's profile photoIon Zubizarreta's profile photo
16 comments
 
Hi +Stephen Gale. I think that you don't obtain the 2nd to last response. You obtain all the responses. But it looks like you have the 2nd to last. Check it!! I think that it is because when you run getResponses(); obtain all the responses that are recorded.
Add a comment...

AlV0nx

Problem/Coding Help  - 
 
When using a jdbc connection to an external database, eg:

var conn = Jdbc.getConnection('jdbc:mysql://yoursqlserver.example.com:3306/database_name', 'username', 'password');

Is it possible to get the password from a some sort of property file (or something else), so that it is not readable in the script?

As I understood, if the spreadsheet is shared, people can view the script source aswell.

thank you for your insights
1
Andrew Roberts's profile photoAlV0nx's profile photo
4 comments
AlV0nx
 
Thx for the replies, I'll try it out
Add a comment...

Joseph Simonini

Problem/Coding Help  - 
 
I have a few google spreadsheets that I would like to email every night at 10 pm automatically. Can anyone help with this? 
1
Ramon Vigo's profile photoSean Dennison's profile photo
6 comments
 
Hi Joseph, so the solution you're looking to do is absolutely possible. Tomorrow I'll be working from around 8am to around 1pm (CST), if you'll be available, we can work remotely on a joint App Script Project to get this solution working for you. Feel free to reach out if you're interested.

Otherwise, a "gotcha" I experienced myself when setting up a time-based trigger (this is the mechanism you'll be using to trigger when you will send your emails via App Script): Triggers that are set up programmatically in the App Script's global scope can be repeatedly added each time your script runs (depending on the implementation), this can be a bad thing! Imagine getting 1 email the first time the script runs, 2 emails the second time, 3 the third, etc, etc, etc. We can cover this tomorrow if you're up for it! 
Add a comment...
 
I want to delete some videos from my drive account using Google Appscript.... Can anyone help on this.. make it clear as I'm novice to Appscript...  
1
Add a comment...
 
Hi,

I'm having an issue with Apps Script to manage PayPal IPN. I opened a question on stack overflow.

I discussed with +Romain Vialard who also do that but we faced the same issue.

If you can have a look and may be an idea ?????

#dopost   #urlfetch   #paypalipn   #appsscript  
1
Romain Vialard's profile photoStéphane Giron's profile photo
6 comments
 
no need :-) few word in a return can change everything.... :-)
Add a comment...

Manuel Nardone

Problem/Coding Help  - 
 
Hi, i try to search a google script for manage the workflow approval request, for example the expense request.
Any suggest?
1
Manuel Nardone's profile photo
8 comments
 
Anees, yes i check but i can't find anny solution for my problem
Add a comment...

Kathryn van Nieuwkerk

Problem/Coding Help  - 
 
Word Clouds

Anyone had any luck creating word clouds? I've been playing around with it for an idea I had, but no luck getting this to work:
2
Kathryn van Nieuwkerk's profile photo
8 comments
 
+Martin Hawksey​ that's good info, I was perhaps going to play around with viewpoint width for text size. The formatting needs a bit of work. 
Add a comment...