How to reference a cell as the file or folder id for DriveApp?

Question

I have an app script function that includes IDs for both a doc file tmplate and a Google Drive folder location. I want the ID to be dynamic based on a users input via a cell reference in a Google Sheet.

Is it possible to call this value using a cell reference?

Example: Settings!B9 = Google Doc Template ID

//This value should be the id of your document template that we created in the last step
  const googleDocTemplate = DriveApp.getFileById('INSERT CELL REFERENCE');
  
  //This value should be the id of the folder where you want your completed documents stored
  const destinationFolder = DriveApp.getFolderById('INSERT CELL REFERENCE')

Answer 1

This can be easily achieved by making use of the getValue method.

Therefore, you will have to add the following lines to your code:

var ss = SpreadsheetApp.openById(SS_ID);
var sheet = ss.getSheetByName(SHEET_NAME);
var fileId = sheet.getRange(ROW,COL).getValue();
var folderId = sheet.getRange(ROW,COL).getValue();

The getRange method is used to get the range where these values are and in order to retrieve the actual value, the getValue method is used.

Reference

• Apps Script Sheet Class - getRange();

• Apps Script Range Class - getValue().

Answer 2

Another option would be to use the getRange(a1Notation) function and pass Settings!B9 directly as a cell reference:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const file_id = ss.getRange("Settings!B9").getValue(); // get value in "Settings!B9"
  const folder_id = ss.getRange("Settings!C9").getValue(); // get value in "Settings!C9"
  const googleDocTemplate = DriveApp.getFileById(file_id);
  const destinationFolder = DriveApp.getFolderById(folder_id)
}