In google apps script is there a way to populate list item with data from a mysql table. For instance instead of hard coding all the employee names populate list from table data.

var form = FormApp.openById('aqdlslcDE390-Qdmldmad');
var item1 = form.addListItem();
item1.setTitle('Designation')
.setChoices([
item1.createChoice('Manager'),
item1.createChoice('Sales Associate')
]);
var item2 = form.addListItem();
item2.setTitle('Employee')
.setChoices([
item2.createChoice('Employee10'),
item2.createChoice('Employee11')
]);

Thank you.

Does anyone know why a trigger does not work when submitting a Google form. I can't get my script to run when a form is submitted. It's worked once or twice. But then nothing 

I am looking for someone who would be able to help me take a google sheet and export the data to a separate google docs based on room number. I know there should be a script or a formula to do so, but I can't seem to figure out how. I have attached my sample sheet.

I want a document for each room number that lists all the equipment located in that room. Thank you in advance!

I need some help, I am running a import script on google sheets an all of a sudden I am getting a error (Service error: Spreadsheets (line 17, file "Import")) never had this issue before, anyone have any ideas?


function Import() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("Durham");

sheet.getRange('A1:FF300').clearContent();

var ssraw = SpreadsheetApp.openById("SpreadsheetID");

var sheetraw = ssraw.getSheetByName("Durham_Data");

var range = sheetraw.getRange("A1:FF300");

var data = range.getValues();

sheet.getRange("A1:FF300").setValues(data);


Hello everyone!

After spending a couple hours and asking multiple people I still find myself stuck on solving an inconvenience in my spreadsheet.
I have a planning file that requires input on one sheet and then displays this on another sheet. In order to display this on the other sheet you'll need to fill the ID number, which the formulas use to get the data from the sheet used for filling.

Since the whole sheet is based on formulas, it is important to copy all the formulas when adding a new line with information.
For this I got the following script: This script copy's the selected line and pastes it one line under, thus keeping the formulas.

var ss = SpreadsheetApp.getActive();

function onOpen() {
var menu = [{name:"Copy selected line", functionName:"addRow"}];
ss.addMenu("New line", menu);
}

function addRow() {
var sh = ss.getActiveSheet(), lRow = sh.getActiveCell().getRow();
var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
sh.insertRowsAfter(lRow, 1);
range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});
}


Here's the problem:
People have to first create a new line with information in the information sheet, and then create a new line to enter the ID number in the display sheet.

What I would like:
People to only add a new line in the information sheet (called 'Actionlist') and that it then also generates a new line on the display sheet (called 'Overview').

But the catch is:
The whole file is depended on the ID numbers which goes: 1, 1.1, 1.2 and 2, 2.1, 2.1 and so forth. Each number is for a different person/division and has to stay in place. This means that if someone creates a new line after 1.2 in the 'Actionlist' sheet, there should also come a new line after 1.2 in the 'Overview' sheet.

What I think:
I think that after this code in the script should follow something that checks if the current sheet is 'Actionlist', and then select the A column value (which is the ID), and then looks for this value in the 'Overview' sheet to copy and paste a new line also after that one. Then people only have to change the ID number to the one they assigned on the Actionlist sheet.

The script fires after clicking the generated menu button while having an cell selected.

Images below for clarification:

Hopefully everything is clear! Many thanks in advance!

Good morning! I was hoping to create a simple form to capture the minimum data sets my employer requires before we issue out a PO number then, once the minimum data sets are captured, I wanted to have something show up/pop up/email (whatever is easiest to program) that said "Your submission was received and your PO number is ###". We use sequential numbering for POs if that makes things easier. I've searched around the different forums and have found similar questions, but nothing that quite fits the bill yet. Any help you guys could provide would be awesome!

I also posted this in the "Google Docs Help Forum" and someone suggested I re-post it here as well.

Thanks!


Dustin

Hello All,

I posted this on Stack Overflow but figured I would also reach out to this community.

Let me first express that programming is not my speciality so I will do my best to explain what I'm working on. At my employer, we have a company activity calendar on a free Gmail account and then we have separate accounts through Google Apps (paid) for each employee. I'm working on a script that pulls the events from said calendar, adds them to an agenda-styled email, and then sends the email to the Google Apps accounts. Our goal is to allow the employee to open the email, click an event he/she is interested in, and then RSVP to the event. It might be worth noting, the script is saved/running under the free account.

We have most of the code working but the RSVP feature is not appearing when the event is opened. We are making sure that the Google App account is logged in when the agenda email is viewed and that the account is an invited guest to the event. I suspect this is due to some type of authentication issue in the way we are generating the eventURL. I also believe this isn't working because the script is running as the "user" who already owns the calendar. If these two suspicions are true, I'm not sure how to fix it.

Here is the code for your review. I've removed the HTML formatting and our private calendar addresses.

function ordinal_suffix_of(i) {
var j = i % 10,
k = i % 100;
if (j == 1 && k != 11) {
return i + "st";
}
if (j == 2 && k != 12) {
return i + "nd";
}
if (j == 3 && k != 13) {
return i + "rd";
}
return i + "th";
}

function myFunction() {
var calendar = CalendarApp.getCalendarById('[email protected]');
var now = new Date();
var oneWeekFromNow = new Date(now.getTime() + 604800000);
var twoWeeksFromNow = new Date(now.getTime() + 1209600000);
var threeWeeksFromNow = new Date(now.getTime() + 1814400000);
var eventsOneWeek = calendar.getEvents(now, oneWeekFromNow);
var eventsTwoWeeks = calendar.getEvents(oneWeekFromNow, twoWeeksFromNow);
var eventsThreeWeeks = calendar.getEvents(twoWeeksFromNow, threeWeeksFromNow);
var body = '';

for (i = 0; i < eventsOneWeek.length; i++) {
var day = ordinal_suffix_of(Utilities.formatDate(eventsOneWeek[i].getStartTime(), "GMT-0400", "d"));
var splitEventId = eventsOneWeek[i].getId().split('@');
var eventURL = "https://www.google.com/calendar/event?eid=" + Utilities.base64Encode(splitEventId[0] + " " + "[email protected]");
var eventTitle = eventsOneWeek[i].getTitle();
var eventStart = Utilities.formatDate(eventsOneWeek[i].getStartTime(), "GMT-0400", "MMMMM");
eventStart += ' ' + day;
eventStart += Utilities.formatDate(eventsOneWeek[i].getStartTime(), "GMT-0400", " 'at' hh:mm a");
body += '';
}

for (i = 0; i < eventsTwoWeeks.length; i++) {
var day = ordinal_suffix_of(Utilities.formatDate(eventsTwoWeeks[i].getStartTime(), "GMT-0400", "d"));
var splitEventId = eventsTwoWeeks[i].getId().split('@');
var eventURL = "https://www.google.com/calendar/event?eid=" + Utilities.base64Encode(splitEventId[0] + " " + "[email protected]");
var eventTitle = eventsTwoWeeks[i].getTitle();
var eventStart = Utilities.formatDate(eventsTwoWeeks[i].getStartTime(), "GMT-0400", "MMMMM");
eventStart += ' ' + day;
eventStart += Utilities.formatDate(eventsTwoWeeks[i].getStartTime(), "GMT-0400", " 'at' hh:mm a");
body += '';
}

for (i = 0; i < eventsThreeWeeks.length; i++) {
var day = ordinal_suffix_of(Utilities.formatDate(eventsThreeWeeks[i].getStartTime(), "GMT-0400", "d"));
var splitEventId = eventsThreeWeeks[i].getId().split('@');
var eventURL = "https://www.google.com/calendar/event?eid=" + Utilities.base64Encode(splitEventId[0] + " " + "[email protected]");
var eventTitle = eventsThreeWeeks[i].getTitle();
var eventStart = Utilities.formatDate(eventsThreeWeeks[i].getStartTime(), "GMT-0400", "MMMMM");
eventStart += ' ' + day;
eventStart += Utilities.formatDate(eventsThreeWeeks[i].getStartTime(), "GMT-0400", " 'at' hh:mm a");
body += '';
}

GmailApp.sendEmail('[email protected]', 'Testing Calendar Agenda', body,{'htmlBody':body});
}

Thank you in advance for any insight/help you can share! We greatly appreciate it!

I just noticed a warning in Chrome Dev Tools while I was testing an add-on about not using KeyEvent.keyIdentifier:
"'KeyboardEvent.keyIdentifier' is deprecated and will be removed in M53, around September 2016. See https://www.chromestatus.com/features/5316065118650368 for more details."

I visited the provided link within the warning (under Chrome version 54):
https://www.chromestatus.com/features/5316065118650368

"Remove the WebKit specific KeyEvent.keyIdentifier field. It is not specified in a current specification and has a number of interoperability concerns. The KeyboardEvent.key field is a suitable specified replacement for this field."

My guess was an old jQuery library version was throwing this warning. My script was using:
ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js and ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/jquery-ui.min.js so I visited https://developers.google.com/speed/libraries/ and replaced the recommend versions of:
ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js
ajax.googleapis.com/ajax/libs/jqueryui/1.12.0/jquery-ui.min.js
and link:
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.0/themes/smoothness/jquery-ui.css" />


I have a web page that displays data from Google Sheets. The page is available to users within my organization, and the sheets are shared with everyone in the organization. This has been working well, but recently I started getting the following warning in the console;

"This spreadsheet is not publicly viewable and this request will soon require an OAuth credential."

Does this really apply to sheets that are shared with the entire organization?

This actually seems to be somewhat less secure. For example, it appears I can choose to get a blanket authorization that causes the user to give the page access to all of their sheets, or they have to explicitly select sheets to grant the access to. Since the web page accesses multiple sheets, and these are sheets that the users don't even know of, but have access to, requiring them to grant access to specific sheets isn't practicle. So now they have to grant access to all of their sheets? That doesn't seem to make sense.

While these sheets aren't publicly accessible, they are organizationally accessible.

If anyone can provide clarification it would be greatly appreciated. Thanks

Dear all,

I have a web app developped with HtmlService that runs fine since years for many users.
On first autorisation screen, it requests :
- Know who you are on Google,
- View your email addesss,
- View and manage your spreadsheets in Drive,
- Connect to an external service

Today, one user can no more open the app because he alsways has a new autorization screen that requests : "Have offline access".

He tried on another computer, clean cache and cookies, remove the app and try again, no success.

Do you know what happens ?

Hi Everybody,

Everyone knows this error is due to any cause? and whenever inporthtml waited a long time to get results. Thank you.



We have created a small script(custom functions) for commonly used Geo Data such as
1. Fetching place from zip code
2. Capital from country name
3. Currency from country name etc...

Hope you will like it. :)

http://goo.gl/sBM1RI

Hi developers!
We are working on the next Ultradox release which will go live in November.
The new version will allow you to create enterprise-ready apps that play nicely on mobile and desktop by simply assembling a number of building blocks!
This is demo that I've created to show some of the upcoming features:
http://www.ultradox.com/app/Demo
Ultradox has been designed from scratch for Google Apps and can be extended using Google Apps Script.
We are have just started our trusted testers program and I'd like to invite all of you to join!
If you are interested, please fill out this form:
https://docs.google.com/forms/d/e/1FAIpQLSdzXoCX55-N2bGhaqUHbmB7UhvxMEkMcRJOvyKILXb8t8lYuA/viewform

If you have any questions or comments, please let me know.

Thanks all,
Daniel

Hi All, so i create a code that will extract email and display it in HTML table. My problem is that i need to make a pagination for the emails when it reach let's say 25 emails per page. how can i do that? Please help! :(

Here's my HTML code.

//Get latest email with attachments
google.script.run.withSuccessHandler(function (data) {

// Creating the loader
document.getElementById("loader").className = 'loader';
document.getElementById("text-loader").innerHTML = '<br>Depending on the size of your Inbox this may take a minute or so ....';

//Check if has Data Object
if (data != null) {
document.getElementById("mydiv").innerHTML = "<table style = 'width:100%;'>" +
"<tr><th>" +
Object.keys(data[0]).join("</th><th>") + "</th></tr>" +
data.map(function (d) {
return Object.keys(d).reduce(function (p, c) {
return p + "<td>" + d[c] + "</td>";
}, "<tr>") + "</tr>"
}) +
"</table>";
} else {
document.getElementById("mydiv").innerHTML = "No Email Found";
}

//Hide Preloader
document.getElementById("loader").className = 'hide-loader';
document.getElementById("text-loader").innerHTML = '';
}).getThreads();

Hi guys, I'd like to know a custom formula for conditional formatting that compares a cell (D2) to the one above (D1) and if they equal, I'd like to get a range (B2:I2) to turn into a specific color. What would be the formula for that? 

SHEETS SCRIPT PROBLEM - I can clear from the script, but not from a cell on the sheet. I know this will be an embarrassingly easy solution, but I don't have an answer. Any assistance will be graciously entertained.

https://docs.google.com/spreadsheets/d/1gub-N3lw4iL1i6g8WDHKkkZYubiSvUzEyJENNWip79Q/edit?disco=AAAAAx7IXD0#gid=0

New Advanced Service: Tag Manager API

This is Google's support how-to video:
https://www.youtube.com/watch?v=ibj2k4lCf-Q

Here is the Google Apps Script documentation:
https://developers.google.com/apps-script/advanced/tag-manager

Here is Google's Tag Manager API documentation:
https://developers.google.com/tag-manager/api/v1/

Here is a helpful non-Google blog, "Tag Management 101":
http://www.signal.co/resources/tag-management-101/

Dear All

Thanks for all your help, i have completed my first Google Docs App, i published it using the "Publish as a Web App" option and i got the below URL.

https://script.google.com/macros/s/AKfycbx-284bWj-NHTUDSnEmMUNT8jvNkTfnTR6HBACpfqG3wW_vuG4/exec

But i am not sure how to add the web app to my Drive, so that all my document have this menu and features i have created.

Thanks
Saai Murugan

Hello Everyone,

I am looking for some guidance on a specifc trigger action is google sheets.

If a cell is changed to a specific value ("Good"), how can I trigger an email (Sent to a specific recipient) that contains that row?



hey guys...so I was wondering if anyone else could take a look and see if they could give me a hand here. If you see my most recent comment here: https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!msg/docs/fx-3uyIZGS8/pv0ZWDn3BwAJ you can see exactly what I am trying to do

Basically I came up with 6 scenarios that should cover everything I am trying to do. If some one is able to help me out with even a few of these scenarios I could probably make the others work myself. I am just kind of really lost on where I can even start here. Anyways here are the 6 scenarios, and any help is greatly appreciated!

Scenario 1: If an edit is made to a row in the sub sales lead, it looks for the matching row in the master sheet, and updates with the changes that were made.
Scenario 2: If an edit is made to a row in the master sheet, it finds the matching row in the sub sheet and updates with the changes that were made
Scenario 3: If a sales lead is assigned to a specific user, it copies that data over as a new row in the sales sub sheet
Scenario 4: If a sales lead is removed from a specific user, it removes that row from the sub sheet
Scenario 5: If a new row is created in a sales sub sheet, it copies that data over to the master sheet
Scenario 6: If a row is deleted in either the sub or master sheet, they are removed from the doc