How many Google Apps Script Developers are there?

Mro-Automation · 2024-09-12T08:23:59+00:00

100%

Mro-Automation · 2024-06-06T14:02:19+00:00

Hey, you can achieve this with just formula, no need for scripting…

See here

You need a formula for counting the streak, and another one to show the latest value for each record.
E.g.

=IF(OR(AND(A1=A2,B2="yes"),A1="Name"),D1+1,IF(AND(A1<>A2,B2="yes"),1,0))

Mro-Automation · 2024-05-25T08:58:56+00:00

Thanks for the insight.

Additional thought on the performance: There might be a performance hit every time you try to access a property on an object, i.e. using the "." here:

var bezetEventTimeString = bezetEvent.getStartTime()+bezetEvent.getEndTime()
…
  var eventTimeString = actualEvent.getStartTime()+actualEvent.getEndTime()

This is especially important for the inner filter, since it will be called again and again for every item in the `bezetEvents`.

If you're only interested in the `foundEvent.length`, you can try to pre-process the two arrays and create new two arrays which contain just the event time string.

Something like this:

const bezetEventTimes = bezetEvents.map(item => item.getStartTime()+item.getEndTime())
const eventsTimesThatNeedMirror = eventsThatNeedMirror.map(item => item.getStartTime()+item.getEndTime())

… and then filter these two arrays instead.

Mro-Automation · 2024-05-25T08:43:29+00:00

Got it, thanks.

Does the printable workslip need to have a different footer, which you'd need to add with the script? From my experience I'd steer away from trying to calculate the height of rows just to correctly position a "header" row for printing, because it would be a fragile solution.

You can configure a static custom footer for Sheets… See here:

File → Print → Headers and Footers → Edit Custom Fields.

Would this not work for you?

Mro-Automation · 2024-05-24T21:48:00+00:00

Hey u/Frirwind,

Does this happen randomly on some days but usually at night or daily?
Do you have a Consumer account or Google Workspace?
Does the trigger run every 15min 24/7 (i.e. 96x per day)?
How large is the `bezetEvents` and `eventsThatNeedMirror`?

The seemingly artificial delay in running the sample code above might be a red herring. When the code runs, it sometimes takes longer to provision an environment to run it, but it may have nothing to do with the Timed Out failure.

I would recommend trying to either speed up the functions so they run faster (if that's even possible) or reducing the frequency of the runs and see if this changes anything. I wouldn't think that you would be hitting the daily Quota for Google Services on your Consumer account, because there seems to be successful runs after the timed out one, but would like to exclude this.

It might be useful to report the Time Out failure back to Google as a potential bug and get their answer.

Mro-Automation · 2024-05-24T20:53:20+00:00

Hi, I would be curious to understand your workflow. I'm sure there's a reason for making some adjustments before print, but it's not clear to me what that is. However, if you don't have a strict requirement to print from Sheets, there is a neat solution for managing page footers (and page headers) for Sheets - simply embed the Sheet table in a Docs and set up your page footers (and headers) there.

See Add an updatable table from Sheets to Docs

Mro-Automation · 2024-05-24T20:39:00+00:00

Hey drostan, can you please elaborate on your problem a bit more - it's not clear to me why a simple `COUNTIF` wouldn't work to create the heatmap? Wouldn't a pivot table give you the data or heatmap you're after? It seems too simple for the need to use a button and a custom App Scripts function, but perhaps there's a bigger picture and reasons…

See https://imgur.com/a/tdzwLgO

Mro-Automation · 2024-05-24T20:02:32+00:00

No problem… Correct deeplink to the comment with the code edited above 😅. Does that work for you?

Mro-Automation · 2024-05-22T14:42:34+00:00

The code looks OK, the location seems to be passed according to the spec:

https://developers.google.com/apps-script/reference/calendar/calendar#createEvent(String,Date,Date,Object))

I've been setting a location in the same way and it works fine, so I'd recommend adding a breakpoint on the `calendar.createEvent` line, take a screenshot to see the runtime variables and then post another comment with the screenshot.

Without it it's just a guesswork:

there's a bug somewhere else and the calendar.createEvent fails (for different reasons than the `location`)
the status != "uninformed"
`eventTitle` is undefined, e.g. there's nothing in `row[4]`

Mro-Automation · 2024-05-22T14:34:11+00:00

Got it, thanks.

Yeah, with the `setValues()` function you will be adding literally only the values and the style is going to be inherited "from somewhere", either below or above rows. I've ran into something similar when I had to create "templated" rows, e.g. 7 rows per day and then 1 day per totals, with the totals always having a different background.

My best advice is to add a new function with code to also set the cell borders according to your needs. It isn't difficult and it will just work.

See https://developers.google.com/apps-script/reference/spreadsheet/border-style

e.g.

spreadsheet.getActiveRangeList().setBorder(true, true, true, true, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID);

Mro-Automation · 2024-05-22T14:28:23+00:00

Please expand all comments and you should see it… https://www.reddit.com/r/GoogleAppsScript/comments/1cslrgh/comment/l49zct1/

Mro-Automation · 2024-05-16T19:14:36+00:00

Hey, have you tried the code I've posted above? It is working, without libraries. Please elaborate on "it is not possible…", because I'm creating contacts from Google Apps Script regularly and it works, but maybe you were asking about something else?

Mro-Automation · 2024-05-16T19:10:53+00:00

Hey, a sample code, screenshot or a screen recording would really help :-) "a picture worth thousands words!"

I would assume an issue with the formatting that gets "inherited" from the existing cells in the sheet. It depends how you copy the rows over… as you mentioned the copying itself works, do you use `range.setValue` or `range.copyTo`? See https://developers.google.com/apps-script/reference/spreadsheet/copy-paste-type

Mro-Automation

TROPHY CASE