Categories
Docs

Unsubscribe from Summary of Failure Emails from Google Apps Script

You may sometimes receive a “Summary of failures for Google Apps Script” email notification from Google (apps-scripts-notifications@google.com). The error messages may say:

  • Exceeded maximum execution time
  • Service using too much computer time for one day
  • Authorization is required to perform that action
  • Service using too much computer time for one day

Google Scripts run on Google cloud servers and Google allows fixed CPU quota (time) to a script per user. These time quotas are different for Gmail (90 minutes/day) and Google Apps (3 hours/day) and the daily quotas are refreshed at the end of a 24-hour window.

If you get any of these service errors from Google, it indicates that the script exceeded the total allowable execution time for one day. The script execution is temporarily stopped and the execution is resumed as soon as Google resets your daily quota.

For “authorization required” emails, it likely indicates that the script no longer has permission to access your Google account and you’d need to reauthorize the script to get it working again. Until the script is reauthorized, the notifications emails would continue to arrive.

How to Stop Failure Emails from Google Script

If you would like to prevent Google from sending these failure emails permanently, you need to uninstall the associated Google Script or Google Add-on from your Google account. You will also have to remove the time-based trigger it established to stop the email notifications.

Here’s how:

Step 1: Open a new tab in your browser and open script.google.com to launch the Google Apps Script Editor. You need to sign-in with the same Google Account that is receiving the email notification errors.

Step 2: Go to the Edit Menu and choose All your triggers.

Step 3: Apps Script will ask you to name the script you are using. Just click ‘OK’.

Step 4: The popup window will display a list of all triggers associated with the various Google Scripts that you have authorized in the past. Look through the list for the trigger that is failing.

Step 5. Click the ‘x’ icon next to the trigger and click “Save” to remove the trigger from your account.

The trigger should now be removed, which should, in turn, stop the email notifications from apps-scripts-notifications@google.com – you might receive one or two more after completing this process since the notifications are batched together.

You can close the script editor tab. Note that this process described above will create an Apps Script file called “Untitled project” in your root Drive folder — once the trigger is deleted, you can remove this file as well.

Categories
Docs

File Upload Forms – Getting Started Guide

This step by step tutorial will help you get started with File Upload Forms for Google Drive.

Design the Form

1. Go to forms.studio and design your form using the WYSIWYG editor. You can even embed images and videos in your Forms.

google forms studio

2. Save the form and click Preview Form Design to check the layout of the draft form. Copy the embed code from the Form Preview page on to your clipboard.

Configure the Google Spreadsheet

3. Open the Google Spreadsheet that was automatically created in your Google Drive with the premium license. The form responses will be stored in this sheet.

4. Go to Addons > File Upload Forms > Form Settings and authorize the File Upload Forms script. The script runs in your own Google Account and even a single byte of data never gets shared with anyone.

[imagemap]imagemap[/imagemap]

5. Update the form configuration and save the changes. (The above screenshot is interactive – you can hover the mouse to know more about any setting)

6. Inside the same Google Spreadsheet, go to Tools > Script Editor. Switch to the forms.html file and paste the HTML embed code. Save your changes.

google forms html code

Deploy the File Upload Form

7. While you are inside the Script Editor, go to Publish > Deploy as Web App.  Choose “Me” under “Execute the App as” and choose “Anyone, even anonymous” under “Who has access to the app.”

Click Deploy and make a note of the web app URL. That’s your File Upload Form page that you can share with the world.

deploy google form

For help, please refer to the File Upload Forms documentation. This guide is also available on Google Slides.

Categories
Docs

How to Add Images, Videos and Google Maps in File Upload Forms

This step by step guide explains how you can embed logos, images, videos, tweets, Google Maps, or any other web element in  your File Upload Forms.

To embed an image, go to this free image hosting service and upload the image from your computer. Copy the HTML embed code to the clipboard.

Next open your form inside Forms Studio and drag the <HTML> field into the canas.

html-form-field.png

Expand the HTML field and you can paste the embed code of the image here. Click Save Form and preview to test your image on a live form.

logo-upload.png

You can use the same technique to embed Google Maps, tweets, YouTube Videos or even Instagram pictures. These websites provide the embed code inside IFRAME tags that can be added to the HTML field of the Form.

 

Categories
Docs

How to Edit your File Upload Forms

This step by step guide explains how you can edit your File Upload Forms and add, edit or delete fields in the form. You may refer to a previous guide on how to edit colors of the form.

Step 1: Go to Forms Studio and sign-in with your Google Account. You’ll see a list of all the forms that you’ve created earlier. You can either create a new form or edit an existing form.

forms-studio.png

Step 2: After you’ve created the forms inside Form Studio, click the Save Form button followed by Preview Form Design to test the actual layout of your form.

preview-html-form.png

Step 3: On the Preview screen, scroll to the bottom of the page and copy the embed code to your clipboard.

clipboard.png

Step 4: Open the Google Sheet associated with your File Upload Form, go to Tools > Script Editor and click the forms.html file. Remove all the existing code and then paste the code you copied to the clipboard in the previous step. Save the changes.

html-form-code.png

Step 5: The forms have been updated but changes are not live yet. Go to Publish > Deploy as web app and choose a new Project Version. Then click Update and your update form will go live instantly.

deploy-web-app.gif

 

 

Categories
Docs

How to use Google Analytics with File Upload Forms

File Upload Forms for Google Drive can be integrated with Google Drive and you can easily track how many people have visited your page, what browser they user, where they came from and other useful information.

analytics-google-form.png

  1. Go to google.com/analytics, sign-in with your Google account and select the Admin tab.
  2. In the ACCOUNT column, use the dropdown menu to select the account to which you want to add the property.
  3. In the PROPERTY column, select Create new property from the dropdown menu.
  4. Select Website for Website or Mobile app, enter the Form Title for the website name and put https://script.google.com in the website URL field.
  5. Click Get Tracking ID that will be something like UA-XXXX-YY

Open the Google Spreadsheet associated with you web form, go to Addons > File Upload Form > Settings and add the tracking id in the GA code field (see screenshot).

Check your Analytics Real-Time reports

With the Real-Time reports, you can monitor user activity as it happens.

  1. Sign in to your Analytics account.
  2. Navigate to a view in the property to which you added the tracking code. If you only recently added the tracking code to this property (website), it is likely that there will only be one view.
  3. Select the Reporting tab.
  4. Select Real-Time > Overview.
Categories
Docs

How to Reorder Columns in File Upload Spreadsheet

When a new response is submitted via the File Upload Form, the data is saved in a Google Spreadsheet while the files are saved in your Google Drive. The first row is the header containing the question titles while the other rows are answers, one row per response.

Later, if you edit the form design and add, edit or update form fields, the header row of the Google Spreadsheet will be updated as well.

reorder-spreadsheet-columns.gif

However, as you may notice, the order of columns in the Google spreadsheet may not be the same as the order in your Google Form. If this is an issue, you can manually move the entire columns to the correct position in the spreadsheet.

When a new response is received, the data will automatically go into the right columns.

Categories
Docs

How to Restrict Access to your File Upload Forms

When you publish a new File Upload Form, it is public by default meaning anyone on the web can access your form and submit a response. However, there are few things to protect your Google Form from spam and also make is available only to select users.

1. Add a Password

Open the Spreadsheet, go to Addons > File Upload Forms > Settings and specify a Form Password. The default is NONE meaning the user need not enter a password but you can enter any password here and only users who know the password can fill your form.

2. Add a CAPTCHA

When design the form inside the online form builder, add a CAPTCHA field and your form will be protected from spam bots. The CAPTCHA is powered by Google reCAPTCHA and therefore pretty foolproof.

3. Restrict Form Access

When publishing the file upload form as a web app, you get a few options. Selection the option that works perfect for your case.

Inside the spreadsheet, go to Tools > Script Editor > Publish > Deploy As Web app. Here, under “Who has access”, choose any of the following option:

  • Anyone, even anonymous – Your form becomes public and anyone on the Internet can access and fill your form.
  • Anyone – Only users who are logged into their Gmail or Google Account can fill your file upload form.
  • Anyone, with xyz.com – If you are a Google Apps user, you can choose this option to restrict form access to users who are in your organization.

Troubleshooting:

If you do not see the anonymous option while publishing your file upload form, it is likely because of a setting in your Google Apps domain. The admin may have restricted users from sharing files outside the organization and thus you do not see that option.

Categories
Docs

How to Allow Multiple File Uploads in Web Forms

Say you are a branding agency and you require applicants to upload 3 photographs with their cover later. There are two options. You can either have 3 different file fields inside your File Upload Forms or you can have a single file field that allows users to upload multiple files.

Single File Upload

single-file-upload.png

Multiple File Uploads

multiple-file-uploads.png

The only disadvantage with multiple file upload fields is that a user can upload any number of files so it may be a better idea to have multiple file upload fields.

To allow multiple file uploads, go to Addons > File Upload Form > Form Settings and check the option “Allow Multiple Uploads”.

Categories
Docs

How to Embed File Upload Forms in your Website

You can easily embed File Upload Forms in any website be it a WordPress blog, a Squarespace site, a WooCommerce store, your company’s intranet or even Google Sites.

Your File Upload Form is internally a web app hosted on script.google.com. You can use IFRAME tags to embed this web app into any other page but before doing that, we need to make a few changes in the Form Settings.

This is required because the File Picker, the library that helps users upload files on to your Google Drive, is configured to work on a single web domain and doesn’t allow multiple origin domain.

What that means is if you have a form served from script.google.com and you change the embed domain to, say, example.com, the file upload will only work on example.com and not the script.google.com domain.

embed-form.png

How to Embed File Upload Forms

  1. Open the associated Google Sheet, go to Addons > File Upload Forms > Form Settings and put your website domain in the EMBED DOMAIN field. For instance, if you wish to embed the form on example.com, you need to put http://example.com in the field.
  2. Save the settings and close the window.
  3. Go to Tools > Script Editor and choose Publish > Deploy as Web App and make a note of the web app URL (see screenshot above).

The web app URL has this format:

https://script.google.com/macros/abc/exec

The embed code would be something like this:

Remember to replace the URL in the IFRAME code with the URL of your web app. You can also change the height and width value as per the design of your own website.

Categories
Docs

How to Restrict File Uploads to Certain Type of Files

With File Upload Forms, you can easily build web forms that would let anyone upload files to your Google Drive. The files are uploaded via the Google File Picker and sent straight to a folder in your Google Drive.

File upload fields, by default, would allow users to upload files of any extension. However, if you wish to restrict users to only upload files of selected types, that can be easily configured through form settings.

Go to the Google Sheet, Addons, File Upload Forms, Form Settings and here look for the field Allowed File Types. The default value is ANY meaning all file types are accepted.

Valid file types are IMAGE, AUDIO, VIDEO, PDF, WORD, POWERPOINT, EXCEL, ZIP, TEXT or ANY for all types.

You can also specify multiple file types in the field. For instance, IMAGE,AUDIO,VIDEO would allow the user to only upload images and media files while WORD,PDF would restrict users to PDF and Microsoft Word documents.

Internally, the File Picker looks at the MIME Type of the file as shown in this table.

File Type Valid MIME Types
IMAGE image/jpeg,image/jpg,image/gif,image/png,image/bmp
VIDEO video/x-flv,video/mp4,video/quicktime,video/x-msvideo,video/x-ms-wmv,video/ogg,video/webm,video/3gpp
AUDIO audio/aac,audio/mp4,audio/mpeg,audio/ogg,audio/wav,audio/webm,audio/mp3,audio/3gpp
PDF application/pdf
WORD application/vnd.ms-word,application/msword,application/vnd.openxmlformats-officedocument.wordprocessingml.document
POWERPOINT application/vnd.ms-powerpoint,application/vnd.openxmlformats-officedocument.presentationml.presentation
EXCEL application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
TEXT text/css,text/html,text/plain,text/tab
ZIP application/x-gzip,application/x-bzip2,application/x-tar,application/x-tar,application/zip,application/x-compressed-zip
Categories
Docs

How to Send Email Notifications with File Upload Forms

File Upload Forms for Google Drive can send email notifications when a new responses is submitted. The email message can be sent to any email address, including the form submitter’s email.

file-upload-forms.png

Open the Google Sheet associated with the Form and go to File Upload Forms > Form Settings (screenshot). To enable notifications, check the option “Send Email Notications” and fill the input fields for Email Address, Email Message and Email Body.

You can specify one or more email addresses in the Email Address field, separated by comma. Also, if you wish to send the notification to the email address of the form submitter, you can put {{ Email Address}} in the field where “Email Address” is the title of the question that asks for the user’s email address.

You can also include any form field in the message subject and message body by enclosing the field inside curly double braces.

For instance, if the title of your question is “What is your name?”, put the Hello {{What is your name?}} in the email subject and Martin fills the form, they’ll see Hello Martin in the email subject.

The email body will include all the form fields by default and also the uploaded file will be added to the email as file attachments.

Categories
Docs

How to Change the Colors of File Upload Forms

File Upload Forms use the Materialize CSS framework that conforms to Google’s Material Design. The color palette is based on the material design base colors. The form layout is also responsive meaning they work on both desktop and mobile devices.

file-upload-colors.png

If you would like to change the default color theme of your web form, you would need to update the CSS of your form. To get started, open the Google Sheet associated with your File Upload form, go to Tools > Script and switch to the css.html file.

Here, let’s look at body class which has background color set to #e0f2f1 (a shade of green). You can replace this value with any other hex color to change the background color of your web form.

The form structure has the body, the upper section (ctrlqHeaderMast), the accent line (ctrlqAccent) and the actual form box (ctrlqFormContent).  The submit button is .btn-large.

You can apply the styles to the corresponding class or HTML element to format it. Here are some CSS styles that will change the color theme to dark gray.

body {
 background: #455A64;
}
.ctrlqHeaderMast {
  background: #607D8B;
}
.ctrlqAccent {
  background: ##CFD8DC;
}
.btn, btn-large {
 background: #455A64;
}
.btn:hover, .btn-large:hover{
 background: #607D8B;
}

After you have updated the colors inside css.html, save the file and then go to Publish > Deploy as Web App and publish a new version of the app.

Please remember that the new colors would only be applied after you publish a new version of the web app.

See Google’s own color palette for recommended set of colors of material design.

color-pallete.png

Categories
Docs

How to Add Data Validation in File Upload Forms

When building the form with Forms Studio, you can easily add data validation rules using regex and that will allow to you automatically validate the user’s input. The user will be notified though inline error message of invalid input data before submitting a form.

If you are new here, it would help if you can get a basic understanding of Regular Expressions. They can also be used in Google Docs, Microsoft Word, Google Forms and most text editors.

To get started, sign in to Google Forms Studio with your Gmail account and create a new form or manage an existing form. Here expand any input field and put the data validation rule under the Regex Pattern field.

data-validation-regex.png

For instance, the regex [0-9]{5}(-[0-9]{4})? will match U.S. zip code consisting of five numbers with an optional hyphen and four-digit add-on.

The regex [a-zA-Z0-9]{5,10} will match any set of alpha numeric characters that are between 5 to 10 characters in length.

For exact matches, you can enclose the regular expression in the caret (^) and dollar sign ($) markers. A ^ matches the position at the beginning of the input string and a $ matches the position at the end of the string. If any characters are entered other than the required match, the input would be rejected.

^\d{3}-\d{2}-\d{4}$ will only match social security numbers.

^4[0-9]{12}(?:[0-9]{3})?$ will match all Visa card numbers as they start with a 4 and can have either 16 or 13 digits.

^\d{3}[-.]?\d{3}[-.]?\d{4}$ will match US phone numbers.

^[A-Za-z0-9\s\-\’\.]{5,50}$ will match names having anywhere between 5 to 50 characters. These will however not work with non-English accented characters like Düsseldorf or Köln.

Categories
Docs

Why Gmail is not Sending my Email Messages?

Gmail has certain limits in place and you only send or receive a limited number of emails per day. The daily limits are 2000 email messages for Google Apps (G Suite) accounts and 500 for consumer Gmail accounts.

It is important to know that Gmail sending limits are per user and are shared by all of the user’s email clients, including web apps, software programs, SMTP clients and add-ons that could be sending emails through your Gmail account.

For instance, if you have sent 300 emails through the Gmail website, 50 emails through Microsoft Outlook linked to your Gmail account via IMAP or POP, and another 100 messages through an email alias on a different domain, the maximum number of emails you can send through the Mail Merge addon will be just 50 in that 24 hour period.

If you exceed the sending limit, you’ll get an error message – like “you’ve reached a Gmail limit for sending email” or “Oops.. the system encountered a problem” – and Gmail may sometimes temporarily block you from sending new messages. The limits are not applied at any set time of the day and the user may have to wait up to 24 hours before the limits are reset automatically.

[*] The daily sending limit is even lower – 100 total recipients per day – if you are using Microsoft Exchange or any non-Google SMTP service for routing messages through Gmail email servers.

Email Bounced – Your Message Was Not Sent

If you get a bounced email from nobody@gmail.com – with the message saying “An error occurred, your message was not sent” or “You have reached a limit for sending email, your message was not sent” – it indicates that you have reached the Gmail sending limit for the day.

There’s no workaround to this problem and you’ll have to wait until Gmail resets your email quota. After the quota is reset, you can resend the message to the same recipients and they should be delivered as normal.

Your messages can also bounce if you are sending too many emails to non-existent email addresses. It is thus important to clean up your mailing lists and remove invalid email addresses before running another Mail Merge campaign.

Gmail Error – Message Rejected

This happens when Gmail classifies your outgoing email message as spam. This happens when you have spam text in the message body or if you have a large number of recipients in the CC or BCC list.

Categories
Docs

How to Email a Range of Google Spreadsheet Cells

The Email Spreadsheet add-on helps you automatically email one or more sheets inside a Google Spreadsheet to multiple people. You can configure the add-on to convert and email individual sheets or the entire spreadsheet.

One of the popular features of the add-on is the ability to email the selected range of cells. For instance, if you have a big sheet but you only wish to email a specific range of cells, you can use the Range option to email the selected cells.

Every cell in a Google Spreadsheet has a Row, Column (R, C) coordinate. For instance, the cell A1 has Row = 1 and column = 1. The cell E8 has Row = 8 and Column = 5.

When configuring the Email Spreadsheet Add-on to convert a range of cells, you need to provide the upper left cell in the range and the lower right cell in RC format. For instance, if you would like export all cells in the A1:E8 range, the R1C1 and R2C2 values would be 1,1,8,5 respectively.

Email Spreadsheet Range

In the above example, the selected range is A5:C12 and therefore the export range is 5(R1), 1(C1), 12(R2), 3(C2)

You can further select advanced options to show / hide gridlines, page numbers, etc. Internally, the add-on is powered by Google Apps Script.

Categories
Docs

Google Drive Search for Files

The Google Drive Permissions Auditor add-on uses a Google Script to analyze who has access to your Google Drive files. You can use any of the search operators of the Google Drive API to query for files in Google Drive whose metadata match your search criteria.

1. Find all files in my Google Drive owned by me except those in trash

"me" in owners and trashed = false

2. Search for files that begin with the term “IMG”

name contains 'IMG'

The contains operator only performs prefix matching for a name. For example, the name “HelloWorld” would match for name contains ‘Hello’ but not name contains ‘World’.

3. Search files that contain the word “ctrlq” anywhere in the content

fullText contains 'ctrlq'

The contains operator only performs matching on entire string tokens for fullText. For example, if the full text of a doc contains the string “HelloWorld” only the query fullText contains ‘HelloWorld’ returns a result.

4. Find all image files modified since January 30, 2016

modifiedTime > '2016-01-01T12:00:00' and mimeType contains 'image/'

5. Find all audio & video files that are not owned by me

sharedWithMe and (mimeType contains 'video/' or mimeType contains 'audio/')

6. Search for files containing the text “important” and are in the trash

fullText contains 'important' and trashed = true

7. Search for all Google Sheets in Google Drive (Supported MIME Types)

mimeType = 'application/vnd.google-apps.spreadsheet'

8. Search for files where abc@example.com is an editor (writer)

'abc@example.com' in writers

9. Search files that do not contain the word “ctrlq” in the content

fullText not contains 'ctrlq'

10. Search files that are not JPG images

mimeType != 'image/jpeg'

If you need help with writing search queries, please contact the developer.

Categories
Docs

Google Add-ons Not Working for Google Apps

If your Google Add-ons are not working as expected or if you are unable to install Google Docs add-ons from the Chrome store, it is likely that your Google Apps admin has disabled the setting that allows domain users to use add-ons.

Google Apps Add-ons

Here’s how they can enable add-ons for your Google Account from the Google Apps admin dashboard.

Step 1 – Login to your Google Apps admin console at admin.google.com

Step 2 – Go to Apps -> Google Apps -> Drive and make sure the status is “ON” for everyone.

Step 3 – Go to Apps -> Google Apps -> Settings for Google Drive -> Data Access and enable the following settings.

– Drive SDK (Allow users to install Google Drive apps)
Google Drive apps allow users to open their files in web apps installed from the Chrome Web Store.

– Add-Ons (Allow users to install Google Docs add-ons from add-ons store)
Docs add-ons allow users to use Docs features built by other developers.

The Admin console setting for add-ons controls Docs, Sheets, and Forms; there are not separate settings for each document type.