FAQ Database Discussion Community


Google Sheets API for clickable images in Google Sheets

google-apps-script,google-spreadsheet,google-spreadsheet-api,google-spreadsheet-addons
In Google sheets, you can insert a clickable image to a cell which links to a function in app script. Can we also insert this clickable image and link it to app script from our server using Google Spreadsheet API? Or Can we call app script from our server? https://developers.google.com/apps-script/guides/menus#clickable_images_and_drawings_in_google_sheets...

How to do a sum of a column if a particular cell of each row got a particular value, in a Google Spreadsheet?

function,google-spreadsheet,spreadsheet,google-spreadsheet-api,sheet
I'm trying to make a budget sheet for a trip. I got 2 spreadsheets. 1 contains the steps of the trip. The other contains a dashboard with the sum for each of these 5 groups of steps : "food", "activities", "shopping", "hotels" and "vehicle". My first sheet is like that...

No spreadsheets found when using AppIdentityService to authenticate

java,google-app-engine,google-spreadsheet-api
I can successfully authenticate my app engine app using AppIdentityService. However when I perform a SpreadsheetService.getEntries I receive no entries. Here's my code: SpreadsheetService service = new SpreadsheetService("Spreadsheet editing"); String[] SCOPESArray = { "https://spreadsheets.google.com/feeds" }; final List SCOPES = Arrays.asList(SCOPESArray); AppIdentityService appIdentity = AppIdentityServiceFactory.getAppIdentityService(); AppIdentityService.GetAccessTokenResult accessToken = appIdentity.getAccessToken(SCOPES); Credential...

Google Sheet update data from my server

google-apps-script,google-spreadsheet,google-spreadsheet-api
I create a Google Sheet add-on but now I need to update the sheet when there is new update on my server. I found there are 2 options. use installable trigger to periodically pull values from my server. use form submit trigger. I'm not sure whether this can be used...

Is there a way to reuse Google.Apis.Auth.OAuth2.UserCredential object to authorize SpreadsheetsService?

c#,google-drive-sdk,google-spreadsheet-api
I'm trying to use the Google Drive and Spreadsheets APIs from a C# console app. I'd like to authorize both services using user credentials with a FileDataStore so that I don't have to reauth my app every single time it runs. Below is how I'm authorizing my Drive service object:...

deleting column by header value deletes all columns

google-apps-script,google-spreadsheet,google-spreadsheet-api
My code has (at least) 2 problems. Can anyone offer suggestions? I am trying to learn JavaScript so I would really prefer hints rather then completed code. My goal is to copy a sheet Main into a new sheet called achievement and remove all columns that do not have the...

php-google-spreadsheet-client getSpreadsheets returning empty array

php,google-spreadsheet-api
I'm using the php-google-spreadsheet-client to get the list of google sheets. Here is the code I am using: <?php require 'vendor/autoload.php'; require_once 'google-api-php-client/autoload.php'; use Google\Spreadsheet\DefaultServiceRequest; use Google\Spreadsheet\ServiceRequestFactory; function getToken() { $client = new Google_Client(); $client->setApplicationName('project'); $client->setClientId('client_id'); $key = file_get_contents('key.p12'); $cred = new Google_Auth_AssertionCredentials( [email protected]',...

List names of sheets in Google Sheets and skip the first two

google-apps-script,google-spreadsheet,google-spreadsheet-api
I found code to list the names of all the sheets in Google Sheets (from here): function SheetNames() { // Usage as custom function: =SheetNames( GoogleClock() ) try { var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() var out = new Array( sheets.length+1 ) ; //out[0] = [ "Name" , "gid" ]; for (var...

Google Spreadsheet to pdf with watermark in Google script

google-apps-script,google-spreadsheet,pdf-generation,google-spreadsheet-api
I want to convert the spreadsheet with a watermark/background image, and send + save the generated pdf The converting to pdf worked, but I don't know if/how you can put an image to the generated pdf. This is what i got now: function ExportAndSent(subject, filename, email) { var ss =...

Do you need to migrate to Drive API when using https://spreadsheets.google.com/feeds/

google-drive-sdk,google-spreadsheet-api
The 22nd of January, I received an email with the subject 'Important: Steps to migrate from discontinued Documents List API' from Google Apps . Important: Steps to migrate from discontinued Documents List API Hello administrators, We recently communicated that Documents List API will be discontinued on April 20, 2015. This...

“undefined” when trying to get values from spreadsheet with Google Apps Scripts

javascript,google-apps-script,google-spreadsheet-api,google-form
I'm getting an "undefined" error when I try to get the value from a cell in a spreadsheet. The thing is that if I execute the same command for a different cell I get the value in that cell. The only difference between those 2 cells is the way the...

Google form spreadsheet update script

google-apps-script,google-spreadsheet,google-spreadsheet-api,google-form
I have some code for my form that works well for emailing the manager of an expense report for approval, as well as receiving a URL back with information needed to send the response back to the person who filled out the information on the form. all i need it...

Google Sheets Copy Down Issue

google-spreadsheet,spreadsheet,google-spreadsheet-api
I have a google form that my students fill out daily. All form responses feed into one workbook. From the workbook, I have a spreadsheet for "Clean" data. Which is just the information from the responses sheet needed to graph progress. The formula I am using in A2 is: ='Form...

How To submit Google Spreadsheets via Google Api or via Curl

curl,google-api,google-spreadsheet,google-spreadsheet-api,google-form
I tried to submit it via ajax but it displays cross origin request blocked..! so I can't post via ajax. so I am finding way to post it via Curl or using Google API. Any one have idea then please suggest me or give me example. Below is what I...

Issue manipulating the Spreadsheet connected to a Google Form

google-apps-script,google-spreadsheet,google-spreadsheet-api,google-form
Sorry if this question has been answered before, I did not find any entries with this issue. I'm trying to set up a newsletter subscription service for our non-profit organisation. I'm using a Google Form to collect user input. OnFormSubmit a script on the form is to write additional information...

How to import Drive API Client Library for Java and Google Sheets API version 3.0

java,android,google-drive-sdk,google-spreadsheet-api
After weeks of struggling with the documentation and plenty of forums I found out how to use both the Drive API Client Library for Java and Google Sheets API. I feel that the documentation especially for android is very lacking so I thought it would be useful to make a...

Google Spreadsheet custom function to add csv in spreadsheet

google-apps-script,google-spreadsheet,google-spreadsheet-api
I am using Google spreadsheets, and in my google spreadsheet i want to import data from my RESTful web service. I cant use =IMPORTDATA due to the fact that i need to add a header to the url for auth purposes. This is the custom function i have so far:...

How to get first row from Google Spreadsheets Data API

google-spreadsheet-api,google-data-api
Here is an example: https://spreadsheets.google.com/feeds/list/1a2JzZzUjSIcpROgR5v_M-UmWyT-iokcegIxHJOopZWA/od6/public/full?alt=json The returned JSON data doesn't contain the first row from the spreadsheet. You can view the contents of the spreadsheet in HTML (https://docs.google.com/spreadsheets/d/1a2JzZzUjSIcpROgR5v_M-UmWyT-iokcegIxHJOopZWA/pubhtml) to verify that "first row" exists in the sheet. How can I get the first row from the spreadsheet? There is a...

Formula Cell value is not updating in the reference while Using Google Spreadsheet API with JAVA

java,excel,google-spreadsheet,google-spreadsheet-api
I have this sample code in which I am trying to integrate google spreadsheet API with java. import java.net.URL; import com.google.gdata.client.spreadsheet.SpreadsheetService; import com.google.gdata.data.spreadsheet.CellEntry; import com.google.gdata.data.spreadsheet.SpreadsheetEntry; import com.google.gdata.data.spreadsheet.WorksheetEntry; public class ExpressionExample { public static final String GOOGLE_ACCOUNT_USERNAME = "[email protected]"; public static final String GOOGLE_ACCOUNT_PASSWORD = "xxx"; private static URL cellFeedUrl; public...

Google Apps Scripts - Issue running script when sharing Spreadsheet with view only

gwt,google-apps-script,google-spreadsheet,google-apps,google-spreadsheet-api
I created a spreadsheet app using the Script Editor. The users who I share it with are not that technical and I don't want them able to open the Script Editor and change anything, be it on accident or on purpose. If they don't have access to edit the file...

fetching specific columns using listFeed in googlesheet using java api

java,google-spreadsheet,google-spreadsheet-api
I want to fetch only specific columns based on header in spreadsheet. I tried the following code ListQuery query=new ListQuery(url); query.setFields("FieldName"); but it gives the following error: Exception in thread "main" com.google.gdata.util.InvalidEntryException: Bad Request Fields query parameter is not supported at com.google.gdata.client.http.HttpGDataRequest.handleErrorResponse(HttpGDataRequest.java:602) at com.google.gdata.client.http.GoogleGDataRequest.handleErrorResponse(GoogleGDataRequest.java:564) at...

Google Analytics api add-on times out

google-analytics,google-spreadsheet,google-analytics-api,google-spreadsheet-api
When I run the Google Sheets (Google Analytics Add-on) the reports run fine, however, when the automated hourly runs are done I get an hourly email that the reports "timed out" as seen below. Error message Service timed out: Spreadsheets...

gspread/OAuth2: authenticated default gmail account (used early in ClientLogin)

oauth,oauth-2.0,google-oauth,google-spreadsheet-api,gspread
I have been using gspread (authenticated via ClientLogin) for a last year. Now I would like to use OAuth2. I've followed tutorial from gspread site: http://gspread.readthedocs.org/en/latest/oauth2.html The problem is that this method creates new "Email address" (in console.developers.google) which doesn't have an access to spreadsheets - all spreadsheets should be...

How to get Google Sheet data into a HTML page using jQuery/Javascript?

javascript,jquery,html,google-spreadsheet,google-spreadsheet-api
Is there a way to obtain Google spreadsheets data into a HTML page using jQuery/Javascript? I couldn't find any examples/resources regarding the same on the Google sheets v3.0 API page. Could anyone help? I have tried to get the sheets feed using the following request GET https://spreadsheets.google.com/feeds/list/key/worksheetId/private/full...

Learn-Rails Tut Error: NoMethodError - undefined method `login' for GoogleDrive:Module:

ruby-on-rails,ruby,google-spreadsheet-api
Here are the first lines of the server output: NoMethodError - undefined method `login' for GoogleDrive:Module: app/models/contact.rb:16:in `update_spreadsheet' Here is the contact model (contact.rb): class Contact < ActiveRecord::Base has_no_table column :name, :string column :email, :string column :content, :string validates_presence_of :name validates_presence_of :email validates_presence_of :content validates_format_of :email, :with => /\A[-a-z0-9_+\.]+\@([-a-z0-9]+\.)+[a-z0-9]{2,4}\z/i validates_length_of...

'str' does not support the buffer interface Python 3 error for oauth

python,google-spreadsheet-api,gspread
import json import gspread from oauth2client.client import SignedJwtAssertionCredentials json_key = json.load(open('Crowds-9569176f5988.json')) scope = ['https://spreadsheets.google.com/feeds'] credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'], scope) #gc = gspread.authorize(credentials) Error: Traceback (most recent call last): File "C:\Users\sony\Desktop\Python\new.py", line 8, in <module> credentials = SignedJwtAssertionCredentials(json_key['client_email'],...

Cannot read property “length” from undefined. (line 39, file “Code”)

google-apps-script,google-spreadsheet,spreadsheet,google-spreadsheet-api
I have written a google Apps Script which is used to filter the data and copy to another sheet. But, Yesterday it was working fine when I open today morning it was showing the error " Cannot read property length from Undefined " onEdt is a user defined function. Don't...

Query for does not contain

google-spreadsheet,google-spreadsheet-api
I have a sheet that queries three others in the same document and selects a bunch of rows where the column G contains no. Ideally I would prefer it be a query that does not contain the word yes or Yes. I have tried using all of the following mechanisms:...

Is there a faster way to create google spreadsheet in specific folder?

google-apps-script,google-spreadsheet-api
Hello, friends! To create a new spreadsheet in specific folder I use following script function create_ss_in_folder(folder, name) { var ss = SpreadsheetApp.create(name); var id = ss.getId(); var file = DriveApp.getFileById(id); var new_file = file.makeCopy(name, get_folder_by_name(folder)); id = new_file.getId(); DriveApp.removeFile(file); return id; } It take over 8.4 second to run, mainly...

OnEdit function Does not recognize pasted (ctrl-p) Values as being a cell edit. Is it possible to get it to do so?

google-apps-script,google-spreadsheet,google-spreadsheet-api
I've got an script in my google sheet that stamps time codes into specific cells when a name is entered in the column next to it... However the script does not work when the names are copied and pasted into the column... is there a work around for this issue...

Google Sheets / Javascript : Unable to search for a specific string in an array using indexOf()

javascript,arrays,google-apps-script,google-spreadsheet,google-spreadsheet-api
I am attempting to: 1) find the object in the array that contains the keyword "First Name" --not quite-- I can do it from a list of files inside of driveapp, but it doesn't seem to work inside of the SpreadSheetApp. 2) then get the index of that object in...

regex matching syntax in google sheets not working

regex,google-spreadsheet-api
I am trying to match cells in a range that contain the word achievement followed by a . then at least one other word. Can someone tell me why this returns NA instead of TRUE when cell A3 contains achievement.blah? =ARRAYFORMULA(A3="achievement(.[[:alnum:]])*?") which would include things like below as correct: achievement.hello...

Can you programmatically generate buttons in Google Sheets cells?

user-interface,google-apps-script,google-spreadsheet,google-spreadsheet-api
I've got a spreadsheet accessed regularly by a team of non-techies and it would speed up our workflow if I could have clickable buttons auto-generated on certain rows (depending on the row's contents) that launch Google Apps Scripts. An elegant solution (HTML Service or UI Service) would be nice, but...

Net::Google::Spreadsheets login failing. need to use two-legged OAuth2

perl-module,google-spreadsheet-api,google-oauth2
I have an application that uses Net::Google::Spreadsheets. It began to fail with authentication errors earlier this week. My understanding is that Google has deprecated some authentication methods, and that we are now to use OAuth2. My application runs on a headless server, so I cannot use the three-legged OAuth2 solution...

How to show multiple Date/Times per location?

google-spreadsheet,spreadsheet,google-spreadsheet-api
Using Google Spreadsheets, I need to enter data structured like the example below. There will be multiple "quadrants" Each "quadrant" can contain one or many "days", Each "day" can contain one or many "times". This data will ultimately be imported in some backend db (e.g. Access DB, SQL, MySQL)....