Stream

Jacob Edward

Problem/Coding Help  - 
 
When I use something like cell.setFormula("=importXML("http....... to insert some data from a website into my spreadsheet, the data isn't converted to regular data that can have other functions like cell.sort() work on it... when I tried to manually copy and paste the data to another cell I got a copy of the formula, not the data.  How would I go about converting the imported data to a regular format so I can use it in scripts?
1
Jacob Edward's profile photo
4 comments
 
+Benjamin Hauch This is the code I've been using:

https://script.google.com/d/1bEUTB0A5YSxyrvQU2VbAYywKi_5FAZ41bQX78y1FAXb5wBIbKTTt0SqL/edit?usp=sharing

Create a new Spreadsheet and change the first line to reflect your spreadsheets id and sheet name.  Do you know why this will work sometimes, but not every time?
Add a comment...

Scott Reid

Problem/Coding Help  - 
 
Content assist stops working after a few minutes.  Below is the beginning of my simple code for clarification:

var ss = SpreadsheetApp.getActiveSpreadsheet(),
    activeCell = ss.getActiveCell(),
    activeCellValue = activeCell.getValue(),
    activeUser = Session.getActiveUser(),

    // Do not use getActiveCell with these variables...very buggy.
    closed_log = ss.getSheetByName('CLOSED_LOG'),
    invoiced_log = ss.getSheetByName('INVOICED_LOG'),
   
    ui = SpreadsheetApp.getUi(),
    activeColumnHeading = ss.getActiveSheet().getRange(1, activeCell.getColumn()).getValue(),
    ssValues = ss.getDataRange().getValues(),
    woNo = SpreadsheetApp.getActiveSheet().getRange(activeCell.getRow(), 1, 1, 1).getValue();
// other code here...

When content assist stops working, all I have to do is go to the top of my code ("var ss = SpreadsheetApp.getActiveSpreadsheet(),") and remove the "," at the end of .getActiveSpreadsheet() and replace it with a ";"...save the file...then replace the ","....save the file, and then content assist miraculously starts working again....at least for a few minutes.  

Am I doing something wrong or is Apps Script just buggy??  I was planning on building a field service log with apps script, but now I'm getting a little worried that it's going to break on me and I'm going to look foolish.  This is just one of three bugs I've struggled with.
1
Scott Reid's profile photoBryan Patterson's profile photo
4 comments
 
+Bryan Patterson Thanks. I'll give it a try.
Add a comment...

Admin FDCA

Problem/Coding Help  - 
 
has anyone tried getting data from stripe with GAS? trying to figure out if it's even possible
1
Dominic Dodge's profile photoAdmin FDCA's profile photo
9 comments
 
Ya! Of course... My code was just inspirational for you to expand on it ;)
Add a comment...

GAS LibraryApp

Problem/Coding Help  - 
 
SOLVED! Exception: Service invoked too many times for one day: email.
Impossible!

We are using a script in a Google Apps Business account that has sent less than 200 Emails that day but get stuck in this Error message. 

Who has experienced a similar drawback?
2
GAS LibraryApp's profile photo
9 comments
 
The assumption confirmed in writing:

After upgrading to Google Apps for Business, it may take several months for the limits of your Google Apps accounts to be upgraded (regardless of the type of billing plan):

If your domain has six or more users, the limits will be upgraded after the first full-month billing cycle.

If your domain has fewer than six users, the limits may be lower for multiple billing cycles. The limits will be upgraded after the domain has been cumulatively billed for the equivalent of six full-month user subscriptions.

A full-month billing cycle includes the first day of the month through the last day of the month. For example, the 2nd through the 30th does not count. 

https://support.google.com/a/answer/166852
Add a comment...

Massimo Coletti

Problem/Coding Help  - 
 
In our Company, we have several scripts runing under a timer trigger. Few days ago, after two or three runs during the day, they are canceled due to "excedeed computer usage ".
Since the volume of data has not changed nor has the script logic, perhaps something changed in how Google acounts for time? We have a business licence, and this is annoying, as we can't rely on this kind of applications.
Anyone experienced similar problems?
1
Bruce Mcpherson's profile photoPatrick Martinent's profile photo
4 comments
 
+Massimo Coletti  one way to circumvent this is to spread your triggers across a number of Google Apps Account.
Add a comment...

Yan Vernet

Problem/Coding Help  - 
 
Hi. Why does it take so long when my spreadsheet is openning and changing active sheet ?
At first open, it takes long long time to refresh the display !
I use a "on open" script to activate a sheet based on conditions, but it does not work correctly because of this delay to open and refresh display.
Is someone knows how to fix it ?
tks
1
Bruce Mcpherson's profile photoYan Vernet's profile photo
7 comments
 
Thanks a lot for your help.
using your script shows a faster execution. !
Add a comment...
 
The use of a continuation token with DriveApp service is very clearly broken as reported in issue #4116 on Google Apps Issue and Feature Request system.  Please star this issue if you like to see it resolved.

https://code.google.com/p/google-apps-script-issues/issues/detail?id=4116

This is a real shame since using DriveApp to iterate through files/folders works significantly better than DocsList, allowing me to iterate through 10s of thousands of files/folders in just a few minutes and never hit any quotas. 

My end goal is to find "orphaned" files/folders.  I have successfully searched through ~45K files to find ~1.7K orphaned files in about 2.25 hours without hitting any limits/errors.

* The exact same code using folders and the folder iterator fails as described in the issue linked above.

#DriveApp #GoogleDrive   #GoogleApps  
3
Joel Asaro's profile photo
 
This issue still hasn't been picked up, but I thought I would add a note that I worked around it using the Advanced Drive Service to get access to the Drive API.  There is some sample code on the following page that shows how to iterate through folders.  The example Root folders but can be easily adapted for all folders.

https://developers.google.com/apps-script/advanced/drive

To get past the 5 minute script runtime limit I an just storing the pageToken using the PropertiesService.
Add a comment...
 
How can I get my custom function script to pass a range as input from the sheet instead of 4 cell references? #googlesheets   #googleappscript  +Jean-Pierre Verhulst +Riël Notermans 
1
Jean-Pierre Verhulst's profile photoIan Fischer's profile photo
6 comments
 
For a range-version of the first function, try this:

function WKD(range) {
    var arr = range[0];
    var bin = arr.map(function (x) {
        return x ? 1 : 0
    });
    var res, countZero = bin.countItem(0);
    if (countZero == 3) {
        throw 'not enough values to calculate';
    } else if (countZero == 4) {
        res = '';
    } else {
        res = calcTimeNoRound(arr[bin.indexOf(1)], arr[bin.lastIndexOf(1)]);
    }
    return res;
}


and the second function:

function calcTimeNoRound(start, end) {

    var interval,
        msecPerHour = 1000 * 60 * 60,

        dateObj = [start, end] 
        .forEach(function (el) {
            if (!isDate(el)) {
                throw "'" + el + "'" + " is not a valid timevalue";
                return;
            } else {
                interval = end.getTime() - start.getTime();
            }
        });
    interval = interval / msecPerHour;
    interval < 0 ? interval = 24 + interval : interval; //deal with negative values (overnight work)

    return interval;
}
Add a comment...

Ankit Sethi

Problem/Coding Help  - 
 
Please help on this :
 
Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "required", "message": "Login Required", "locationType": "header", ... (use muteHttpExceptions option to examine full response)
1
Rafael de Alemar Vidal's profile photoAnkit Sethi's profile photo
25 comments
 
I had a similar issue. Try making g a copy of your script file. This way you will force another authentication 
Add a comment...

Michael Hari

Problem/Coding Help  - 
 
Is there anyway for me to set a form's item value? I'm trying to maintain a form where if a user comes back to the form, they will have their previous responses pre-filled from a spreadsheet I maintain. I know about building prefill URLs, but I'm not going to be able to send the user an email with such a link. 

I find it strange that there isn't a form.item.setValue() method. 
4
1
Jean-Pierre Verhulst's profile photoMichael Hari's profile photoAndrew Hyland's profile photo
2 comments
 
Thanks. I simply made another Google App Script that will give the generated URL as a link to the user.
Add a comment...

Roberto Diana

Problem/Coding Help  - 
 
I need some help to modify this tracking log sheet ... I need to track modifications and copy to another sheet. Thanks. 
1
Jean-Pierre Verhulst's profile photoRoberto Diana's profile photo
2 comments
 
+Jean-Pierre Verhulst
Your help was great ! but ...

1st. Only track changes on LastColumn (J2, J5, J9 not C2, D3 o E2)
2nd. e.user  not work (?)

Finally I use this part of your code and now work fine
  event.source.getSheetByName("slog")
        .appendRow(["Last modification: " + actRng.getA1Notation() + ' by ' + Session.getActiveUser()])

thanks ...solved !!!
Add a comment...

Scott Reid

Problem/Coding Help  - 
 
function onOpen() seems to be broken.  I'm just trying to add a drop down menu that will trigger a sidebar.  It worked for a day or two and then just stopped working.  Below is my code:

var ui = SpreadsheetApp.getUi();

// other code here that works....

function onOpen() {
  var menuBar = ui.createMenu('Service Log');
  menuBar.addItem('Sidebar Comment', 'addSidebarComment');
  menuBar.addItem('Reassignment Request', 'reassignmentRequest');
  menuBar.addToUi();
}

When I manually run the onOpen() function from the script editor, the menu appears and everything works as expected.  For some reason the built in onOpen event is not triggering the onOpen() function.  The install-able trigger does work, but I can't use it since I'll have multiple users and I don't want them going into the script editor and manually adding the trigger.....and then authorizing the script...too many steps.  

Not sure how to test onOpen().  I tried a try catch block using MailApp to send the error message, but didn't receive an error...just silently failed.

I Googled the hell out of it, but came up with nothing other than a bunch of unresolved tickets.  Any work arounds??

Any help would be appreciated.
1
Scott Reid's profile photo
5 comments
 
Update:  I trashed the idea of using a sheet as a data store and created a Google Cloud SQL database(maybe a little overkill).  I think the spreadsheet would have been fine for my purposes(very few transactions), however I couldn't get past the openById() issue.  Cloud SQL seems to work really well, but I'm a little (very) concerned that GAS JDBC is still listed as "Experimental" even though it's on the migration path for ScriptDb.  Bleeding edge can be very painful...I don't like painful.    
Add a comment...

Jose Luis Costa

Problem/Coding Help  - 
 
Hi People,

Anyone know How change the pivot table's range with script??
Thanks 
1
Jose Luis Costa's profile photoFabio Krauss Stabel's profile photo
3 comments
 
that works too :) cool!
Add a comment...
 
DriveApp.createFile() with GOOGLE_ MimeType is broken
It's been for almost a month, I just noticed, we have a few broken scripts because of this issue.

Please, star this issue if you agree it's important, to get more attention.

Any Googler around can help, please?

Regards, Fausto
3
Scott Reid's profile photoJean-Pierre Verhulst's profile photo
4 comments
 
Starred
Add a comment...

Jacob Edward

Problem/Coding Help  - 
 
https://developers.google.com/apps-script/reference/spreadsheet/range#sort(Object)

Problems with the .sort() spreadsheet function...  This is the code:

function myFunction() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getActiveSheet();
  doc.getActiveCell().setFormula("=IMPORTHTML(\"https://www.google.com/finance\",\"table\",2)");
  var range = doc.getDataRange();
  
  range.sort(2);
}

It's suppose to sort the table by the second column, instead it's inserting three blank rows before the table...
1
Alexander Ivanov's profile photoDavid Tew's profile photo
13 comments
 
It is simply writing to the spreadsheet with .setFormula and then reading the values in the range created by IMPORTHTML; and then writing over the these values so that now they are only values and not a formula in the range to which .sort(2) is applied.

Of course now the formula is no longer there, and so will not refresh (unlike your version).

(maybe this code is in part an illustration of the prior explanations of the effects seen by Jacob Edward?)
Add a comment...

Jacob Edward

Problem/Coding Help  - 
 
Google Spreadsheets:
Cannot call method "getActiveSheet" of null. (line 19, file "Code")
and
Cannot find method open(File). (line 9, file "Code")

+Amit Agarwal After reading this article on how to import HTML tables (which was awesome),
http://www.labnol.org/internet/import-html-in-google-docs/28125/ I've been trying to figure out how to set the imported table's range by using this code, but I don't really understand how it works:

https://script.google.com/d/1Sp_ERzJ-6FPsdbuacjF53nMejryozYbdJryRAIczdz6PFPR57pXoDFx2/edit?usp=sharing

because I kept getting this error for what is now commented out:
>>Cannot find method open(File). (line 9, file "Code")<<

and because I kept getting this error:
>>Cannot call method "getActiveSheet" of null. (line 19, file "Code")<<

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#open(File)

what I was trying seems like it should have worked... any ideas?
This video tutorial explains how you can import web data - like HTML tables and lists - from external web pages into your Google Docs sheets with a simple formula.
2
1
István Maczkó's profile photoJean-Pierre Verhulst's profile photoJacob Edward's profile photo
34 comments
 
The spreadsheet consists of sheets (minimal 1). Only sheets have ranges. So somehow you will have to refer to a sheet. 
Add a comment...

Ian Fischer

Problem/Coding Help  - 
 
Is there a way to make a floating row of cells? Depending on how many rows are filled with content the row after the last nonblank row can enter a total? and it updates with a filter function without throwing an error because its going to overwrite some data?
1
Alexey Isachenko's profile photoIan Fischer's profile photo
2 comments
 
+Alexey Isachenko  I like it because "work smarter not harder," is my motto. I'll have to sell that to the customer though because they want the totals at the bottom.
any way to insert rows when using the filter function?
Add a comment...

Sunil Kumar

Problem/Coding Help  - 
 
I have a spreadsheet in which i have column with 100 dates .I'm trying to use a category filter i get a lengthy list. Instead of that i put values into it by using setvalues(). the search says No DATA .Please help
1
Add a comment...

Riël Notermans

Problem/Coding Help  - 
 
ContactsApp permission issue.

For one user, every call to ContactsApp gives an 'user does not have permission for this action' error. The script did authenticate. I cannot find any setting that would prevent this.

Any ideas?
1
Romain Vialard's profile photoGAS LibraryApp's profile photo
Add a comment...

Ryan Leach

Problem/Coding Help  - 
 
Does anyone have any experience with scraping google plus community membership from google app scripts?
1
Add a comment...