Is it possible to take info from one sheet (that was submitted by form) and then automatically put that info into a calendar (even it was made in another sheet doc) Would be nice to use google Calendar but can't seem to figure out how to make all appropriate fields transfer
Cheers

Hello Everyone!
I've two google forms, when I submit the first form i'd like to be redirected to the second form.
thanks

Dear friends,
I'm using Google Sheets script editor to write a simple piece of code to GET a csv and populate a sheet with it.
The code works just fine when the target url is this:
var url = 'https://dl.dropbox*/myfile.csv';

If I change it to a url that is still HTTPS, but exposed from a test server and therefore using a self signed certificate, I get this SSL EXCEPTION:

[16-12-21 06:43:09:611 PST] Execution failed: SSL Error https://ec2..../../.../myfile.csv (line 56, file "Code") [0.351 seconds total runtime]

The only difference that I can think of is that the latter is using a self-signed certificate, while Dropbox most likely isnt ;).
What do I have to do to make it work in those cases too?
Thank you so much,
Roberto

Hello All,

I am facing an issue with SitesApp in Google App script.

When I execute below line , It's listing all the sites, including the recently deleted sites. But I only need active site (Not deleted sites)

SitesApp.getAllSites("domain name");

Can anyone please tell me how can I extract the list of active sites only?

Thanks

I am curious if anyone can help me. I have a spreadsheet that gets updated from a google form(form responses 1). I then have a sheet (shipping upload) in the workbook that I wrote some script that copies the main response sheet and then filters the results based on two parameters. One parameter is on a YES in column A. This was what I was trying to use to filter out all items that have been shipped. The problem I have, is I don't know how to write a script that if I were to write YES on the (shipping upload) sheet that it would find the appropriate cell in (form responses 1) and update it. All I can find is a direct cell for cell column/row match but this won't work because I delete the rows that met one of the two parameters. What I need is something that matches the timestamp column (B) (because it is always unique to each response) and then updates that appropriate cell in A column on Form Responses 1. Below is the script that I am using, this is my first time doing this so all suggestions to improve this are welcome.

function onOpen(){
colors();
onOpen2();
clearRows()}

function colors() {
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 0; i < 20; i++) {
if ((i % 2) == 0) {
sheet.getRange('A1').setBackground('white');
sheet.getRange('B1').setBackground('white');
} else {
sheet.getRange('A1').setBackground('white');
sheet.getRange('B1').setBackground('white');
}
SpreadsheetApp.flush();
}
}

// The onOpen function is executed automatically every time a Spreadsheet is loaded
function onOpen2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [];
// When the user clicks on "addMenuExample" then "Menu Entry 1", the function function1 is
// executed.
menuEntries.push({name: "Shipping Update", functionName: "clearRows"});

ss.addMenu("Shipping Update", menuEntries);
}


function clearRows() {
Copy();
clearRows1();
clearRows2();
}
function Copy() {
var sss = SpreadsheetApp.getActiveSpreadsheet(); //replace with source ID
var ss = sss.getSheetByName('Form Responses 1'); //replace with source Sheet tab name
var range = ss.getRange('A1:U999'); //assign the range you want to copy
var data = range.getValues();

var tss = SpreadsheetApp.getActiveSpreadsheet(); //replace with destination ID
var ts = tss.getSheetByName('Shipping Upload'); //replace with destination Sheet tab name
ts.getRange(1, 1, data.length, data[0].length).setValues(data); //you will need to define the size of the copied data see getRange()
}

function clearRows1() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Shipping Upload');
var rows = s.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();

var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[0] == 'YES') {
s.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
};
function clearRows2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Shipping Upload');
var rows = s.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();

var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[15] == 'Yes') {
s.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
};



I've been using an IMPORTHTML to pull 2 tables from a simple website for a few months now. Occasionally I'll have a few issues, but for the most part it's been working fine.

Since yesterday it seems to have hit a brick wall and I have a dozen people using copies of my sheets and now they have no data.

The website I'm pulling from is here:
http://www.kuilin.net/cc/clan.php?tag=29yr22qy

There's 2 tables (1 and 2). I use a script to call the function and paste it into cell A1 of the tab CCData but all I have been getting is Loading... for the past 2 days (I use the import HTML and XML in several spreadsheets pulling from several websites). I must have used my quota and google shut me off or there's something going on with their server.


Here's a copy of my sheet:
https://docs.google.com/spreadsheets/d/1pWppQ_GSjt9fMDZXQbVzajABuW7pxXVTYSX2PQ4AjI8/edit#gid=1524906089

the table is supposed to look like this:
https://drive.google.com/file/d/0B4JEQWonOq6cQjI4UUg3TDlERlk/view?usp=sharing

What I am trying to do is instead of loading the importhtml into cell A1 is just use the urlFetch app service but I can't for the life of me get it to work. I'm fairly novice in scripts and I've been figuring it all out as I go, but when it comes to actually getting the HTML data, I'm beyond my abilities.

I feel there should be a simple way to do this that will actually work, can anyone assist? My current script is called "loadActive" that puts the importHTML in

Hello!

Not sure if anyone will be able to help me, but I'm looking to write a script that will automatically importrange data after a certain date (located in Col3 in the master sheet).

Originally, I was using a =query(importrange(...) formula but the formulas would intermittently display a #value error and then the actually data. The data would populate but then after a couple minutes the formula produced the #value error. Whenever I refreshed the page, the data would auto populate again.

This is the formula I was using:

=query(importrange("1GtJr40SYvWlqNQqeBsBf15GWWhAm7RtYU5SZlrkAx-E","VIF Entry!A:D"),"select * where todate(Col3) >= date '2014-05-01' ")

After reading some forum posts, it seems as though writing a script could be a good option and I found an importrange script that will work, but I have NO experience writing script and would like to include the importrange criteria of only importing "cases after 5-1-2014."


Here is the Importrange script I found:


function getdata() {
var files = DriveApp.getFolderById("folder location of files").getFiles()
while (files.hasNext()) {
var file = files.next();
var shoot = SpreadsheetApp.openById(file.getId());

var sourcesheet = SpreadsheetApp.getActive().getSheetByName("source data sheet name");
var sourcerange = sourcesheet.getRange('range);
var sourcevalues = sourcerange.getValues();

var destsheet = shoot.getSheetByName('sheet name of import location');
var destrange = destsheet.getRange('range');
destrange.setValues(sourcevalues);
}
}

How can I add the date criteria?? Again, no experience with writing script here and open to other suggestions.

Thank you so much in advance!!!

It is a great topic for debate for all the time, whether to choose UX or UI for your website. Before moving further we need to know what actually these acronyms stands for. The term UI stands for User Interface Design while the term UX refers to User Experience Design. But both of these are contemporary for each as both are crucial to any product closely together.

#UX #UI #Best #Website #UserExperience #WebDesign #UserInterface #Designer #Prototyping #Mockup #Wireframe

Hello all,
I'm looking to hire someone. I have four different google forms for booking clients but need the right script written so when my client submits the form it automatically gets transferred to google calendar. I need all information on the same calendar. Each form representing a separate color and if I could get the forms to be sent out weekly that'd be awesome. Hopefully, this can be done.
Thanks
Levi

How can I format input from a web form stored in Sheets?

Specifically:
A) If a user puts in a phone number as 8005551212, I want it to be stored on my spreadsheet as (800) 555-1212.
B) If they use upper-case letters in an email address, I want it all to be converted to lowercase.
C) If they enter in a name and hit the space bar before going to the next field, I don't want that space to be entered into the form. (instead of "Bob ", I want it to be "Bob")

There has to be a simple solution. I can't seem to find one.

I have an issue with exporting an excel sheet to PDF and sending through an EMail. When attempting to convert the file only the header row is shown, none of the data below it.

var file = DriveApp.getFileById(spreadsheet.getId()).getAs(MimeType.PDF);

MailApp.sendEmail('email', 'Equipment Update', 'Spreadsheet Attached', {
name: 'Testing',
attachments: [file]
});

I am sure this is something simple I am overlooking, any clarification on the usage of this functionality would be greatly appreciated.

Populate a ui field with a google contacts list:

Hello everyone! I'm very new to coding and have little to no HTML experience, and was told to post here. The relevant bit of code is below, which works just fine, but I want more. I want the user to have the option of adding people to the email that this script sends. I can already get that to happen if they know the email address, but what I'd like is, much like my google inbox, for them to be able to type a name and then have that email address added to the email.

I know how to capture contacts into an array, but how to get that array into an HTML window, turn it into a searchable database of sorts, and then how to get the user responses captured back into variables that I can then plug into the email is still beyond me. I've been reading up on HTML templates, pushing variables, creating drop downs etc etc, but there's a lot of vocab that I don't know. Is my thinking on the right track? If someone can point me in the right direction I think I can figure it out (the code below does none of the things I'm talking about yet...but it'll be my starting point).

//Email Send
var ui2 = SpreadsheetApp.getUi();
var response2 = ui2.alert('Email' , 'Ok, the sheet has been updated! Do you want to send an email?' , ui2.ButtonSet.YES_NO);
var emailaddy = sheet.getRange("E1").getValue();
var TC = "[email protected]"
var message = "Hello " + PM + "! Your friendly (mostly) DRM team has just updated your friendly (never, really) sheet!" +
" It should be accurate as of the time listed near the top right. If it's wonky, please create a paper list of choice expletives," +
" crumple it into a ball, and hurl it at your computer screen. Thanks!";

if (response2 == ui2.Button.YES) {

MailApp.sendEmail(emailaddy + "," + TC, TC, "[TC] " + PM + "\'s Sheet Update", message)

return

}

else {

return

}


}

We have a google site with pages, can we redirect every member to his particular page on connection.
we can create a data base with emails and url's page : on connection redirect every member to his page.


I'm having a problem detecting empty cells. I'm watching the 4th column for edits. When data is entered into the 4th column, the 7th column is updated with today's date. This works perfectly well. However, when data is deleted from the 4th column, my script cannot detect this change. Despite the cell being empty, it is not recognized as empty by my script. I've noticed that {old data} gets stuck in this cell despite not normally being visible to the user. I'm not sure if this has any effect on what I'm trying to do or how to deal with it. You can see the code I'm using below:

function onEdit(e) {
var sheetName = 'Assignments'; // name of the sheet the script should work on
var colToWatch = 4 // watches for edits made in col D
var colToStamp = 7 // timestamp in col E
var colToStamp2 = 8 // col F; Debugging
if (e.range.columnStart !== colToWatch || e.source.getActiveSheet().getName() !== sheetName) {return;}
if (e.value==null | e.value=='' || e.value==0) { // Fails to work.
e.source.getActiveSheet()
.getRange(e.range.rowStart, colToStamp)
.clearContent();
e.source.getActiveSheet()
.getRange(e.range.rowStart, colToStamp2)
.setValue("Empty");
}
else {
var writeVal = new Date();
e.source.getActiveSheet()
.getRange(e.range.rowStart, colToStamp)
.setValue(writeVal);
e.source.getActiveSheet()
.getRange(e.range.rowStart, colToStamp2)
.setValue("Not Empty");
}
}

Does anyone know if Google Scripts Apps source code is backed up somewhere?

I'm working on a project and I want to know whether the source code is backed up..

Hi there,
A beginner question:
Is it possible to make the link from the code below clickable?
This is just plain text now.

ui.alert('File: ' + file.getUrl());

Help adapting a function for cleaning up the new sheet after it has been formatted.
I copied a roster from a board game cafe where I'm volunteering. I've been trying to adapt it so it fits my needs. The only difference between my version and the original is that mine only has 1 available shift each day, while the original has 4-6 shifts. I thought it was only a matter of changing the numbers in the code, but I don't really know anything about coding, so I'm not having any luck. If anyone could tell me what numbers to change, I'd be very happy.

This is the original sheet
https://docs.google.com/spreadsheets/d/1TgI9tYIx_m3GDQQGHvkFWz8MzSaAnRrwFRbeVwgNO48/edit?usp=sharing

And this is the version I've been working on:
https://docs.google.com/spreadsheets/d/1vtsTQUdVUKua4X5CKDusvfijC4Pz1bNWtILS2knjwZk/edit?usp=sharing

The original code looks like this:

/*
function for cleaning up the new sheet after it has been formatted.
*/
function cleanUpSheet(sheet){
var firstTuesdayEvent = sheet.getRange("C2").getValue();
//Delete content from days that are not part of the month.
for(row = 3; row <= 43; row = row + 8){
for(col = 2; col <= 14; col = col + 2){
if(sheet.getRange(row, col).getValue() == ""){
sheet.getRange(row-1, col-1, 8).clearContent().clearNote();
}
}
}
//Check to see if first tuesday is in 2nd week.
if(sheet.getRange("C2").getValue() == ""){
sheet.getRange("C10").setValue(firstTuesdayEvent);
sheet.getRange("C14").setFontWeight("bold").setNote(eventNote);
}

//Check to see if last tuesday is in 6th week.
if(sheet.getRange("D43").getValue() != ""){
var lastTuesdayEvent = sheet.getRange("C34").getValue();
sheet.getRange("C34").clearContent();
sheet.getRange("C38").clearFormat().clearNote();
sheet.getRange("C42").setValue(lastTuesdayEvent).setFontWeight("bold");
sheet.getRange("C46").setFontWeight("bold").setNote(eventNote);
}
//Delete last week(s) if not needed.
if(sheet.getRange(35,1).getValue() == ""){ //Case only needed for February when it starts with a Monday and only has 28 days.
sheet.deleteRows(34,16);
}else if(sheet.getRange(43,1).getValue() == ""){
sheet.deleteRows(42,8);
}
}

I'm using Better Log library (https://sites.google.com/site/scriptsexamples/custom-methods/betterlog) and I would like to change limit of rows in log to more than 50 000 - Google Spreadsheet limit is ~2 million rows.

How can I achieve that? Simply by copying code to script instead of using library and changing variable SHEET_MAX_ROWS? 

Zzapps wishes you a merry xmas and a happy new year.

And, make a team selfie! Playing with Firebase and the Vision API, we created our xmas tree :)

Unfortunatedly iOS is not supporting webRTC, but still, use another device then...;)


https://zzappsxmastree.firebaseapp.com


Archive Script Trigger Issues

Hello,
I have an archive script that runs exactly the way I'd like it too, but I think I am having trigger issues.

I have a "watched" column on a sheet named "Todd Template". When this value becomes "3", the script copies this row to an "Archive" sheet, and then deletes the original Row that sourced this cell from "Todd Template"

My issue is that this script works fine if I manually enter "3" in the "Days Aged" column, but if this column is a calculation, such as networkdays etc, the script will NOT run, even if the value is "3".

I have tried changing the trigger from On Edit, On Change, Time etc.....but it will only run if I manually enter a "3" in this cell.

Any help is greatly appreciated.

Thanks in advance.