Google apps script How can I upload a dynamically created Google Apps Script?

I want to create a script dynamically and then upload it as a Google Apps Script. When I try this, either If I set the mime type correctly the link looks good, but doesn't work (file not found). If I create a text file, that is just what it is - a text file, not a script file. Do I need to wrap my code in an extra layer so that the editor can work with it? Thanks Michael

Google apps script Apps Script Gadget not working on a custom website

i have this problem, and it seems that i'm not alone. If i embed a simple apps script form with some textarea,buttons and labels in a custom web url www.mydomain.com/mypage...the page simply don't show nothing and the Chrome console report that error: Refused to display document because display forbidden by X-Frame-Options. Instead if i see the page from the original url: https://sites.google.com/a/mydomain.com/mysite/mypage i see the apps script correctly. Anyone solved that problem o

Google apps script Styling a custom spreadsheet menu item using Google Apps Script

I have a custom menu on a Google Sheets: function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var csvMenuEntries = [{name: "Save as CSV file", functionName: "saveAsCSV"}]; ss.addMenu("Save New Emails", csvMenuEntries); } I would like to style the menus I create. Is this possible and how would one go about it? All I really want to do is have a custom colour or background for the button to make it stand out.

Google apps script Launch Google Spreadsheet function remotely?

Hopefully this is the last step in automating a little piece of my workflow. Windows scheduler/vbs performs a collection of exports from our SFDC app - done Windows scheduler/vbs imports the exported data into database tables - done Windows scheduler/vbs runs SQL queries to produce the necessary data collections to produce Google Maps app data, then drops the result into my Google Drive as a .csv file - done Added a function to a Published Google Spreadsheet to import the csv data into the spr

Google apps script strange server error GAS

I have a function running in google script. However it does not finish, it enters the main loop and then it stops somewhere, no errors. If i look in the execution description it says server error wait a while and try again. I tried running it several times but it keeps stalling, but each time the last values of i and j are different from the previous stall. I really can't imagine that this is a real server error, it must be the code. function start() { var sheet1 = SpreadsheetApp.openByUrl(

Google apps script Does appscript require G-Docs enabled, even for the basic app

I was trying to run this app from an account that has disabled Google Docs. function doGet() { var app = UiApp.createApplication(); var button = app.createButton('Click Me'); app.add(button); var label = app.createLabel('The button was clicked.') .setId('statusLabel') .setVisible(false); app.add(label); var handler = app.createServerHandler('myClickHandler'); handler.addCallbackElement(label); button.addClickHandler(handler); return app; }

Google apps script How to open an known URL directely, open a drive document by menu?

This methode is Obsolete How to acces directely and open a drive document by menu URL or ID ? function onOpen() { // Add a menu with some items, some separators, and a sub-menu. DocumentApp.getUi().createMenu('OAR') .addItem('MAJ Date + OAR', 'test') .addItem('MAJ Date Auto', 'testdate') ***.addItem('Accès Direct URL', 'href=/d/1udvLSpaZNgalg6NId JMJvkkw3i9oCaEKa-A_CAC79CYkvsZKD545dyZN/edit?')*** .addToUi(); }

Google apps script Google Apps Script Calendar Description - Line break

I would like to separate the description of a calendar event onto different lines. At the moment I am retrieving responses from a Form to populate the description field. I'd like each variable description to be on a different line I have looked at the article How to escape JSON string? but as my responses vary as the form responses differ (and the fact I am at a beginner level) I can't work out a way to achieve it. function CreateEvent() { var vSS = SpreadsheetApp.getActiveSpreadsheet(); va

Google apps script Can a standalone script create a trigger for another script which is bound to a spreadsheet?

I am trying to create an installable onEdit trigger for a spreadsheet bound script. I would like to do this programmatically with a separate, standalone script. It looks like this should be possible according to documentation: Note that, unlike for a simple onOpen() trigger, the script for the installable trigger does not need to be bound to the spreadsheet. To create this trigger from a standalone script, simply replace SpreadsheetApp.getActive() with a call to SpreadsheetApp.openById(id)

Google apps script How to get HTML formatted table in Google Apps Script into a document?

I am trying to add the formatted table in the script to a document. But using the append.Paragraph just puts the text with all the HTML tags in it. Ideally the goal is to have the form that feeds this display the output on the page the form is on. But I started here thinking I can redirect from the form to a page that has this document and sort of do the same thing. function rowOf(team1, dataRange) { var ss = SpreadsheetApp.openById("1FyJm85IyKCMskYA4xM-Nj9TxiJt1Qorik7U1gDgbX4o"); var sheet

Google apps script Sorting Results of listFilesByName in Google Sheet Script

I am making a tab in a Google Sheet to keep track of files in a particular folder. I have successfully modified a script I found online to get the list by folder id, but I can't seem to figure out how to get the results to appear in order by name. Here is the code I'm using but I'm putting the folder id in place of myFolderId: /** * List all files in Google Drive folder. * * @param {string} folderName (optional) Name of folder on Google Drive * * Adapted from: * http://ctrlq.org/cod

Google apps script Is Google Spreadsheet script method clear({commentsOnly}) to delete comments on a range not working?

I'm trying to make a basic Google script function that deletes the comments on the first cell. This doesn't work: function deleteCommentinCellA1() { // Get cell A1 on the first sheet var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange("A1"); cell.clear({commentsOnly : true}); } even though the other clear(options) provided by Google script work fine. I tried the same function with deleting format, content and even checked the other

Google apps script Client ID no longer exists

When I try to run an Appscript I get error .. [17-02-08 01:00:35:160 PST] Open the following URL and re-run the script: https://accounts.google.com/o/oauth2/auth?client_id=317559754348->0p1ti3fmjae175i06hn07jrbia6701q6.apps.googleusercontent.com&response_type=code&redirect_uri=https%3A%2F%2Fscript.google.com%2Fmacros%2Fd>%2F1pgAT7ZCwiKrHx_7Iys770hJNRqTYwn9zioe9Qjvmhzc9rfIxO04P8Uum%2Fusercallback&state=ADEpC8xV9BMj3kqytygKLnjEYT7PX918NJg0i1oSCAsUTRwXcOgdzDStZA3lzGDK98CJ6OOhlDnlYEy

Google apps script Using Google Apps Script and to update Slack status

I'm just starting to learn the Slack API and I'm wanting to use Google Apps Script to set the custom status. Here's the code I've got (somehow there's something wrong with my profile variable: function setStatus() { //https://api.slack.com/docs/presence-and-status var token = MYTOKEN; //https://api.slack.com/apps var apiEndpoint = "https://slack.com/api/"; var myUserID = MYUSERID; var method = "users.profile.set"; var profile = {"status_text": "test"}; var payload = {"token": tok

Google apps script SpreadsheetApp.getActiveSpreadsheet() ask access to data on Google

I have really simple line of code called from button in menu: var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); Problem is, this line of code invokes message: "...needs your permission to access your data on Google" And that's what makes me sad. I don't want to access data on Google. I just want to edit one sheet of data. This message will confuse all my colegues. Is there a way how to restrain SpreadsheetApp to use only opened sheet ? How to avoid this message? Maybe it doesn

Google apps script Utilities.formatDate - incorrect year in the formatted string

In Google App Script consider the following date formatting code function testDateFormatter(){ // Sun Dec 31 2017 20:02:06 var date = new Date(1514746926811) var dateString = Utilities.formatDate(date, "CET", "YYYY-MM-dd"); Logger.log("The date is : " + date + ", after formatting it is " + dateString) } The result is: // // The date is : Sun Dec 31 2017 20:02:06 GMT+0100 (CET), after formatting it is 2018-12-31 // note 2018 instead of 2

Google apps script Google Sheets - I created a clear cell function but it is not working

I want to create a function that automatically clears a range of cells when a corresponding cell changes from 'Yes' to 'No'. The range that I want to trigger this function is... i5:i9, i13:i14, i18 The range that I want cleared IF this cell contains 'no' is n5:n9, n13:n14, n18 Both these cells are on the same sheet called 'Wax'. I have written the following script and assigned it so a button on the sheet but when I press it the script runs and the cells do not delete. function clearautocr

Google apps script Appmaker: Only render filtered results on table load

I have a table of customers, with a column 'account owner', with which I'd like to filter the table of displayed customers so that only the customers assigned to a particular owner will be rendered. When an account owner signs in to the app, the app will detect the active user, find all customers with 'account owner' === active user and list those customers. So far I've got this code on the page containing the table 'onAttach': var datasource = widget.datasource; datasource.query.filters.accou

Google apps script Is there a way to show a modal dialog in Gmail add-on

Is there a way to show a modal dialog in Gmail add-on, similar like in Spreadsheets add-on SpreadsheetApp.getUi().showModalDialog(html, title); Looks like no and the only way to display some UI to a user is to use Cards However, I would like to be sure that it's correct. Couldn't find any article that it's not possible and working solution as well. Thanks for any comments!

Google apps script "The coordinates of the range are outside the dimensions of the sheet" error when sorting

I am trying to create a script to sort a table on the current active sheet/tab but I am getting an error that I could not identify. I keep getting The coordinates of the range are outside the dimensions of the sheet.. My table has data in them from columnA to columnQ. Data in columnA are identification numbers and columnB to columnQ contain formulas. What am I doing wrong with my script? var sortFirst = 7; //index of column to be sorted by; 1 = column A, 2 = column B, etc. var sortFirstAsc =

Google apps script Export all events from a shared calendar, including events added by non-owners

I'm trying to extract Google Calendar event data into a spreadsheet, which works. However, the code I use only exports data which has been created by the owner of the Google Account/Calendar, not events that have been added by other users. Below is a sample of some of the code I am using to get to the calendar and to retrieve events details. Is there any way of achieving the above with details added by all users..? var cal = CalendarApp.getCalendarById("name of calendar"); var events = cal.ge

Google apps script Web App Publishing Issues: How does this work?

Racking my brain on why doGet() changes but doPost() does not when I publish an update. doGet() code var version="0.4.3" // fixed MimeType. function doGet(req) { var respData = { 'function': 'doGet', version: version, request: req, message: 'Getting...', timestamp: (new Date()).toISOString(), } debug && console.log(respData) return HtmlService.createHtmlOutput('<pre>'+JSON.stringify(respData)+'</pre>') } GETs Output of version 0.4.1: Now I

Google apps script How to re-authorize Google Apps Scripts after password reset?

I wrote a script that uses a service account and OAuth2 for Apps Scripts to disable auto-forwarding and enable IMAP on a domain user's gmail settings. The script works. However, when I change the user's password. It stops working and gives me response code 401. From other other question, suggested that I re-authorize the scripts. Reauthorize permissions to Google apps script after password change How do you manually re-authorize the script? I see the user's "access_token" for the Gmail API

Google apps script Hide Sheets Based on a Cell Value

I'm pretty new to learning app script and looked over/tried to edit this script, but I'm not getting my desired result. I have a sheet titled "Menu" where I'm wanting a user to select from three different drop down options in Cell A2 (e.g. Blue, Yellow, Green). I then want to hide the different sheets based on the selection. So if a user selects "Blue" I want only the sheets that start with the word "Blue" to be visible + the "Menu" sheet and the rest to be hidden. Same for Yellow and Green. As

Google apps script How can I modify a trigger so that it emails upon edit, but not so quickly?

I have a script that creates additional files upon submission of a Google Form. It also sets up a trigger for said new document, so that people are emailed when any edits are made to the document after its creation, e.g. ScriptApp.newTrigger("sendEmailOnModification") .forSpreadsheet(SpreadsheetApp.openById(fileId)) .onEdit() .create(); The logic all works, but... the trigger is extremely sensitive. Every time someone makes a single keystroke in the document, it

Google apps script How to declare a variable range to a spreadsheet?

I can't set a variable to return the number to the last row under the K column in google sheets. The code fails on the last line .setValues(exampleText) where I have already defined exampleText as a variable. I have used different methods of setting variables including setting a variable for a column. I get the following error: "Cannot find method getRange(number,number)." The top three rows are an example I tried before. Column = 'K' range = ss.getRange(LastRow, Column) range.setVa

Google apps script Access variable attached to template in apps script

I'm reading through https://developers.google.com/apps-script/guides/html/templates, trying to pass a variable to a template. I've come up with: var html = HtmlService.createTemplateFromFile('RowPopup'); html.row = sendableRow; var h =html.evaluate(); SpreadsheetApp.getUi() // Or DocumentApp or FormApp. .showModalDialog(h, 'Create Documents'); which produces a popup as expected without error. In the template itself I have some JS/jquery functions like the following which I want to be able

Google apps script Deployment-specific configuration for Google Apps Script

I have a Google Apps Script project, for which I have 2 deployments: HEAD (development) VERSION X (production) I need to configure these 2 deployments differently (eg different filenames, usernames,...). What is the best way of doing this? On the Project page of the G Suite Developer Hub I cannot find anything related to "deployment-specific configuration variables". I read somewhere that you can (should?) use a PropertiesService for this, but again I cannot figure out how to (non-programm

Google apps script How to store data in Array using For loop in Google apps script - pass array by value

Edit: answer at the bottom Im experiencing some weird behavior I came across when using google script. I have a 2d array and I insert data into it using for loop. I noticed that if use appendRow(someArray[i]) INSIDE the for loop, everything works as expected. But If try to access data or use appendRow outside of the for loop, it always gives me the data of the last row the ran in the for loop. so: appendRow(someArray[1]) gives same result as appendRow(someArray[2]),appendRow(someArra

Google apps script Count Files in a TeamDrive

I am trying to create a script to count the files in a TeamDrive. Does anyone have a simple codeexample for me. This is my current code - it runs but does not count files. function myTeamDrives() { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); ss.clear(); var folders = Drive.Teamdrives.list({maxResults:100}); var row = 0 for (var i = 0; i < folders.items.length ; i++) { var folder = folders.items[i]; var fID = folders.items[i].getId(); var xx = Dr

Google apps script Fetching AdWords Data for 150+ accounts for a period of 2 weeks in a single Google AdWords Script

We're building a platform that fetches data from the AdWords accounts under our AdWords Manager account and saves the data onto BigQuery to be viewed online via nice charts and tables. As you all know, Adwords numbers need a few days to stabilize. So, every day at 12am we need to refetch all the data for the past 2 weeks and overwrite the data we already have saved for these 2 weeks (we're basically updating the numbers we have). Our AdWords manager account has 150+ accounts under it. So, when

Google apps script Can't publish a Google Script as a Web App

When I try to publish a google script, it keeps loading with a message that says "getting data" or something like that but in spanish. And it never ends, it keeps saying that. I have trying cleaning the cache and cookies, also try with private browsing It never ends loading, then it won't let me publish it. How can I publish the script? This is an image of the message that appears in spanish It says Deploy as a web app and below, getting data.

Google apps script run script only if checkbox is true

I have the increaseline1 and increaseline2 on buttons. I would have it so the button only works if a checkbox is set to True function modifyCell(cellName, increase, checkbox) { var range = SpreadsheetApp.getActiveSpreadsheet().getRange(cellName) var toAdd =-1; if (increase) { toAdd = 1; } range.setValue(range.getValue() + toAdd); } function increaseline1() { modifyCell("Gus!G24!", true); function decreaseline1() { modifyCell("Gus!G24",false);

Google apps script Filter a CSV daily and get the diferences between the last day

I'm working with a supplier who gives me daily a CSV with the stock updates. I need to upload it to my cms website with an import tool. That is cool to make everything faster, but I got some problems: The CSV provided isn't in the same configuration as the import tool. No problem here, I did a filter with a simple Query and Importrange getting exactly the columns I need. The categorization from the supplier is bad, so, I need to automatic list the categories and make my own. No problem here,

Google apps script Macro for google sheet

I have an issue, I created a google sheet named "test". In this file there is a google slide link and I want when I make a copy of the "test" file that the google slide file also make a copy and update the google slide link. My code : function onOpen(e) { SpreadsheetApp.getUi() .createMenu("Custom Actions") //Creates a custom menu item called "Custom Actions" .addItem("Clone Files", "cloneFiles") //Adds a clickable button to call the cloneFiles() function. .addToUi(); //Displays th

Google apps script How to publish a Google Apps Script web app to the G-Suite Marketplace?

First, I know there is a similar question but it is very old and that answer is not valid anymore. I am working on a Google Apps Script web-app (either bounded to a Sheet, or a stand alone one that'll reference a Sheet). I am wondering if it is possible to publish it to the G-Suite Marketplace? All of the documentation I am finding talks about publishing add-ons and what not. I saw this, and many articles/post link to it saying that is how, but it says publishing to the Chrome store is deprec

Google apps script Problem with executing google app script (google slides) after renaming presentation and script

I'm quite new to programming and using google apps scripts. I wrote script that splits selected text in text box (google slides) in different text boxes (each line of initial textbox is a separated textbox). Code was just a modification of examples from developers.google.com. function SelectedTextGrabber() { var selection = SlidesApp.getActivePresentation().getSelection(); var selectionType = selection.getSelectionType(); var currentPage; switch (selectionType) { case SlidesApp.Select

Google apps script Call client side Javascript from Google Apps Script server side code

My Google Sheets Add-On opens a sidebar (which includes a complete React based application in an iframe) that shows a 3d graphic. The Add-On also includes a custom Javascript function that can be called from any cell. Is there a way for the cell function to communicate with the Javascript in the sidebar (eg. a field referenced in the function call is changed, the custom function is recalculated (by Sheets) and the Add-On wants to show some side effect of the calculation (update the graphic) i

Google apps script Google Scripts Editor-Overwrite backup file

I am working with Google Scripts Editor. The purpose of my script is to backup a spreadsheet from Google Sheets to my local as a csv. I would like each new backup to overwrite the previous backup file. Currently the script results in subsequent backup files as the same name but as a copy (Ex. filename (1).csv and filename (2).csv) Any help in overwriting would be appreciated. function myFunction() {// UPDATE THE FOLDER ID for e.g. "My Drive > Docs > Backups" var backupFolder = DriveAp

Google apps script How to get all TextRun elements

Using Google App Script, is there a way to get all the TextRuns (a block of text with the same format) in Google Docs? I'm trying to create a function that batch replaces one specific text format (from black text to white text). I've looked through the GAS reference, but can't find anything that does this. findElement() doesn't allow the ElementType TextRun. And overall TextRun doesn't seem to be a functional/manipulable element, at least in the current ref docs -- even though the Document Struc

Google apps script Script running fine in a sheet, not in another

I have two sheets, both of them in a shared drive. Both have the very same script to send html emails based upon a Google Doc (also located in the shared drive) : first I get the html code, and then send the mail. Everything runs fine in a sheet, but I get a 404 if I run it in the other sheet. I first thought of a permissions issue, but I ran it from two different accounts, and I have the same behavior. The odd thing is that this script used to run absolutely fine... Here's the script : function

Google apps script Unable to fetch bccRecipients, ccRecipients and toRecipients via gmail event object

I am creating a Gmail add-on, basically I want to get bcc, cc and to emails once the user clicks on an email. I have defended onTriggerFunction in the manifest for that, also added metadata scope. But bccRecipients, ccRecipients and toRecipients attributes are null in gmail event object. Manifest "gmail": { "contextualTriggers": [{ "unconditional": { }, "onTriggerFunction": "onGmailMessage" }] } Scope https://www.googleapis.com/a

Google apps script scripting help for a google sheet that pulls data from google drive

so I've been building an inventory spreadsheet to keep track of keys for various locks under all employees with my organization. I basically copied this code from a YouTube video and tweaked a few things to make it work for my needs. I need a little help to just clean up my final sheet for a full view of all data. so what this script basically does it pulls data from multiple spreadsheets in a folder, then posts data to a new sheet combining all data from said folder. I then make a sheet for

Google apps script How to send an email with data input options?

I am a newbie to coding.. I just want to know if there is anyway that we can send an email through Gmail with an option to select and enter data by the users and then save the selected choices in google sheets... e.g i would like to ask these details from the clients and then save them in googlesheet. It shouldn't lead to an external link but the interface should open up in email itself.. e.g Hello there, Please fill in the following details Can anyone please helpme with it...

Google apps script Update code on one google sheet without having to update on other sheets

I apologize if this is a very basic question but would appreciate if anyone could guide me on this. I have created some code for a set of Google Sheets Workbooks, 14 of them to be exact. But I have found that every time a person reports a problem with the code scripts and I can fix it on one the workbook projects I now have to go and fix it on the other 13 workbooks as well. Most of the times it is just a copy and paste from the fixed workbook to the other 13 workbook's script code which doesn't

  1    2   3   4   5   6  ... 下一页 最后一页 共 89 页