What are /r/GoogleAppsScript's favorite Products & Services?
From 3.5 billion Reddit comments

Try to solve questions on this Reddit. But "apps script" is just one half. Yes you will need some basic understanding of the Google methods. But the logic, handeling arrays, if statements, switch, spread, text, regex ect... Is all just javascript. This is a good place to start with your javascript skills.

OK, you need a couple of changes here to set the triggers and change the email collection dates. This will set the trigger for every 5 minutes , I've tried to account for running at minutes under 5 past the hour too.

var MARK_AS_READ_AFTER_MINUTES = 5; var LABEL_TO_MARK="inbox"; function Intialize() { return; }

function Install() { ScriptApp.newTrigger("markMails") .timeBased() .everyMinutes(MARK_AS_READ_AFTER_MINUTES) .create(); } function Uninstall() { https://script.google.com/macros/d/MBqrNPSzGjwZ_-vuH7kZCsXuAQ7im4wST/gwt/clear.cache.gif var triggers = ScriptApp.getProjectTriggers(); for (var i=0; i<triggers.length; i++) { ScriptApp.deleteTrigger(triggers[i]); } }

function markMails() { var age = new Date(); if (age.getMinutes() <= 4){ age.setMinutes(59 - 4 + MARK_AS_READ_AFTER_MINUTES) } else { age.setMinutes(age.getMinutes() - MARK_AS_READ_AFTER_MINUTES); }

edit: changed the code for minutes 0 - 4 instead of 0-5

Maybe this lifehacker article can help. Just be careful to test a sample first so you are sure you are deleting what you want to delete... Edit All Your Calendar Events at Once with This Google Script

Please feel free to rebut my arguments. They seem solid to me, yet, of course, they might contain flaws. I don't mean to antagonize you, let along enrage you, nonetheless, in light of what I have explained below your advice seems terrible to me.

Engineers typically loathe my verbose explanations therefore I chose not to post the following in my original posting. However, now it seem not merely germane, but rather necessary.

I like using Google Docs as a word processor. Sometimes I use Google Docs to write essays. Sometimes I use Google Docs to create documentation. Sometimes I use Google Docs to collaborate with others (instead of emailing). Furthermore, I often use Google Docs’ outline format, styles, and voice typing.

As far as I know, no wiki has all of the features listed above. Put simply: based on my research it seems that wiki's have nothing even remotely as powerful as Google Docs. But still worse I'd need to, for example, spin up a VPS (say at Hetzner) and then worry about running my own server.

I'm not suffering from special snowflake syndrome I would happily run something as simple as DokuWiki, modern Bookstack or be like all the "kool kidz" and go with (Obsidian)[https://obsidian.md/] or Notion.

But those all appear to be vastly inferior solutions.

Google Docs is meets my needs... almost.

I simply want to be sure I have a "working backup" by which I mean, I want to be sure if "things go south" I could reupload my Google Docs to a regular website and maintain all of the links.

You can use GAS on that - see https://gist.github.com/mhawksey/1170597

Or, I would personally use Autohotkey script, because you can use it also anywhere else: https://autohotkey.com/board/topic/44617-grab-stock-quotes/

Problem solved, here is the final code:

var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet();

function onEdit() { var range = SpreadsheetApp.getActive().getRangeByName("ColourSet"); var values = range.getValues();

var numHeaders = 1;

values.forEach(function(row, index) { sheet.getRange(index + numHeaders + 1,2).setBackground(row.join("")); var hexCodes = row.join(""); console.log(hexCodes); }); }

https://imgbb.com/

If you only want to reset the dropdowns you can set the value of those with a blank. The your data validation remans. As example:

sheet.getRange("A1").setValue("");

Don't have the time so look further but look into the difference into var / const / let this can make your code more secure

This just looks like a syntax error. The problem I see is that you aren't outputting to anywhere or returning anything and the if statement looks like it shouldn't work. The if statement as of now always evaluates to true because the last value isn't something that evaluates to false. As of now, it doesn't do anything. You would probably want something like:

var value = sheet.getRange(resultCellRow, resultCellColumn).getValue();

if ( value < 0 ) return "Less than 0"; else if ( value < 6 ) return "Between 0-5"; else if ( value < 11 ) return "Between 6-10"; else if ( value < 16 ) return "Between 11-15"; else if ( value < 21 ) return "Between 16-20"; else return "Greater than 20";

yes definitely and even it does not say anymore that "This Application is not verified by Google"

Try this listing link for my App

https://workspace.google.com/marketplace/app/the_templater/645296923617

i was checking the documentation and looks like you can batch write to firestore: https://firebase.google.com/docs/firestore/manage-data/transactions#batched-writes

i checked the lib you are using with appscript and it is just a wrapper/abstraction/whatever you want to call it to use CRUD operations over this service, you can write your own method to batch write: firestore.createMany(arg1, arg2)

Apps Script has built in types you can reference (you can get the built-in data types from the suggestions), but you have to 'markup' your code using JSDoc syntax. Monaco seems to use a TypeScript linter behind the scenes so you can use TS flavored JSDoc type hinting to some extent as well.

Here's an example:

/**
 * Foo function description.
 *
 * @param {SpreadsheetApp.Range} range 
 *
 * @returns {SpreadsheetApp.Range}
 */
function foo(range) {
    return range;
}

For standard types you can do the following:

/**
 *
 * @param {String} aString - A String
 * @param {Number} aNumber - A Number
 * @param {Object} anObject - An Object
 * @param {Array} anArray - An Array
 * @param {String} [anotherString] - Another String, enclosed with [] to mark it as an optional parameter.
 */
function goo(aString, aNumber, anObject, anArray, anotherString = 'default') {
}

You can even define an 'anonymous' type as follows:

/**
 *
 * @returns {{
 *     index:Number,
 *     list:Array&lt;String&gt;,
 *     item: {
 *         id:Number,
 *         name:String,
 *         value:Number
 *     }
 * }}
 */
function boo() {
    return {
        index:42,
        list: ['abc', 'xyz', '123'],
        item: {
            id:56890,
            name: 'itemName',
            value: 7.35
        }
    };
}

if you create a custom class with proper JSDoc markup, then you should be able to see your JSDoc definitions as suggestions as you use instances of the class.

Checkout the following TypeScript flavored JSDoc guide. I found it to be applicable to the new IDE:

https://www.typescriptlang.org/docs/handbook/jsdoc-supported-types.html

We built a simple way to do this across sheets while maintaining standard ANSI SQL in SeekWell. You can also do things like combine data from a few different Sheets and Slack a summary to a channel or send the output via email.

Most of this is covered in our free tier, though we have other paid features as well (e.g. importing data from a SQL database to Sheets on a schedule.).

The 3rd party app is called Zabbix

https://www.zabbix.com/documentation/current/manual/api/reference/user/login

Previously I was able to use UrlFetchApp following their documentation with no issue.

The issue is that now our organisation has moved it onto a server that requires Google Workspace login.

So where previously I was able to access the API "directly", it is now only able to return the HTML of the 'Sign in with Google' page.

So what I want to be able to do is somehow bypass the signing in with google page and get access to the application.

Yeah don't use Google's editor. You're doing yourself a disservice. Just download clasp, it's really easy to use. Then download VS Code. I think it supports JS out of the box, but you can get some plugins like "Beautify" which automatically formats your code so it looks nice. Or "ESLint" which pops up warnings when it sees some bad code.

If you insist on using Google's editor (which you are just gimping yourself), you can have dark mode by using the extension Dark Reader which is on 4 different browsers (I can only vouch for Firefox). It's pretty good.

edit: your post encouraged me to make this mini-tutorial on clasp

Figured it out if anyone is wondering:

<? currentChains.forEach(function(chain) { ?> <a class="button" href="https://script.google.com/macros/s/abcd1234567890/exec?ssid=abcd1234567890&name=RecentActivities&chainName=<?= chain[0] ?>">Change Chain</a> <? }) ?>

Sure:

curl --location --request POST '[WebhookSubscriptions_URL]' \
--header 'Authorization: Bearer [Token_ID]' \
--header 'Accept: application/json' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'object=obj_name' \
--data-urlencode 'fields=field1, field2' \
--data-urlencode 'callback_url=https://script.google.com/macros/s/\[ID\]/exec'

Ok, found the code:

const API_KEY = 'yourKey'; const SCRIPT_PROP = PropertiesService.getScriptProperties(); const WORKBOOK = ''; const SHEET = '';

// https://script.google.com/macros/s/ scriptID /exec?key=yourKey function doGet(e) { if (!isAuthorized(e)) { return ContentService .createTextOutput(JSON.stringify({ "result": "error", "error": e })) .setMimeType(ContentService.MimeType.JSON); } else { return handleResponse(e); } }

function doPost(e) { if (!isAuthorized(e)) { return ContentService .createTextOutput(JSON.stringify({ "result": "doPost error", "error": e })) .setMimeType(ContentService.MimeType.JSON); } else { return handleResponse(e); } }

function isAuthorized(e) { return e.parameter['key'] && e.parameter['key'] === API_KEY; }

You can see what the methods return in the docs:

https://developers.google.com/apps-script/reference/drive/drive-app#getfolderbyidid

Can you try to run this function:

function testDriveApp() { const docFile = DriveApp.getFileById("1tZcY7ACKsstJ2_xXDMQeHXNa91YhH3jK31ND8wCJtgI"); Logger.log(docFile); }

And paste the logs here? You can get the logs from the script editor directly or from the Apps Script Dashboard here:

https://script.google.com/home

in "My Executions".

The ` XFrameOptionsMode ` was already set to ALLOWALL, so yep, it's still the same behaviour..

Maybe I just didn't understand you 🙏 but it's framed as the only element of a page. I just set a CNAME record through my domain provider to direct a subdomain to the `script.google.com/macros[...]/exec` url

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

Good tutorial my friend, thanks for taking the time to provide resources for those who are interested in learning more about Apps Script.

A few potential changes can be found here: https://script.google.com/d/1XZuZNKtQgq_Jhufi9lmhDgrJOZMXuWRsz6F128hk5UzjGlbp1VxsgbIU/edit?newcopy=true, with the word potential being stressed as often to avoid confusion...less is indeed more.

I'll try to make time to detail why some of those changes might be made a little later on today.

You don't have to make the file public. The users can read or write data in the Google sheet through your Web App with the authorization of your Google account.

Try my test app, you can use this app to write data to below sheet

Try my test sheet, you don't have access to this Google sheet

You are right. How about a short script Snapsooter? Just make a copy and add your ids.

Run.gs

function trigger() { var sn = new Snapshooter(); sn .addId("spreadhseetId1", "folderId1") .addId("spreadhseetId2", "folderId2") .snapshoot(); }

Bind trigger function to triggers in menu 'Edit' of your script file.

Not sure what you mean by time conflict. But, this is how you create a org chart. It's fully done in client js. With apps script, you can create a web app/sidebar to run client side JavaScript and show the org chart. The array to create a chart can come from Google sheets.

I always struggled with date comparisons in JS/GAS. I'd recommend creating a library with Moment.js: https://momentjs.com/ (get the code from their GitHub). You can import the library into any of your projects and it provides amazing functionality for handling dates and makes your life so much easier

Sorry if I misunderstand, but I don't think you're dealing with a webhook for this request. I think you need to send a GET to https://slack.com/api/channels.info with the required arguments. You can use UrlFetchApp to do that. Look at the Tester tab to see how the URL and header should be constructed.

I'm trying to build a functionality where a trello card gets created whenever someone submits a google form.

Something like what zapier is able to achieve. Hence the spreadsheet that is linked with the google form will belong to the user who what to link google form with trello. Hence the spreadsheet belonging to the user may or may not be open for all.

What are /r/GoogleAppsScript's favorite Products & Services? From 3.5 billion Reddit comments

The most popular Services mentioned in /r/GoogleAppsScript:

Google Apps Script

freeCodeCamp

Zapier

Google Workspace

Google Charts

Obsidian

Assertible

Firebase

Purecss

Zabbix

Hackr.io

Lifehacker

ImgBB

Wise

Notion

The most popular reviews in /r/GoogleAppsScript:

Run.gs

What are /r/GoogleAppsScript's favorite Products & Services?
From 3.5 billion Reddit comments