CODEWITHSUNDEEP
google-apps-script
cchantep
cchantep

Reputation: 9168

In Google appscript for application A, how to use functions of SpreadsheetApp?

Considering the development of an app script for Google Form (FormApp), at some point it could be required to store related data (e.g. from processing of submitted responses) in a spreadsheet (one specific to the app script, not the one as form destination).

Using the Google Spreadsheet app to do so could be useful (as then it provides functions to query such data).

Unfortunately, any call on SpreadsheetApp in a script opened in FormApp is raising an error and documentation is unclear about whether such cross app usage is possible or not.

Fallback solution would be to use Google Drive API to access a spreadsheet object, but specific spreadsheet functions (manage structured row of data, query, ...) don't seem to be available from there.

So is there any way to use spreadsheet functions in a FormApp app script?

Upvotes: 0

Views: 317

Answers (1)

Serge insas
Serge insas

Reputation: 46792

You say : "any call on SpreadsheetApp in a script opened in FormApp is raising an error"

That is simply not true...

You can actually run a Form embedded script that uses data from an external spreadsheet without any problem.
You just have to open the spreadsheet using appropriate methods, ie either SpreadsheetApp.openByUrl('SS url'); or SpreadsheetApp.openById('SS ID'); and from there you can open a sheet (getSheetByName('name') or getSheets()[number]) and get values, set values or whatever you want.

You ended your post saying "is there any way to use spreadsheet functions in a FormApp app script?",

I hope you didn't had in mind "using spreadsheet function like the native spreadsheet function you use in cells...." because those are indeed not useable in Google Apps Script, no matter where you save your script. But I'm sure you didn't meant that and that it was just a language ambiguity.

As a simple example, here is a small function written in a Form script and its result as a log.

function getSsData() {
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1xDOaoSl3HbkS95cj8Jl-82rdiui7G0sFz96PIO6iVF4/edit#gid=0');
  var sh = ss.getSheetByName('calNames');
  Logger.log(sh.getDataRange().getValues());
}

The sheet is viewable here, I didn't share the form because I can't share it in view only but any form can be used to test the above script.

enter image description here

Upvotes: 1

Related Questions