Search code examples
formsgoogle-apps-scriptgoogle-sheetsweb-applications

How to make user input Google Sheet database only using by Web App Form and RESTRICT to input directly in Google Sheet?

I'm making web app form using Google Apps Script for database input in Google Sheet. But I want to make users so they can only use the form for data input and prevent them to directly input in Google Sheet (because the Google Sheet has to be shared, and users can directly modify it)

I'm guessing this can be done at the back end, so this is the gs.code of my apps script

var folderID = ""; 
var sheetName = ""; 

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}


function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

function uploadFiles(formObject) {
  try {
    var folder = DriveApp.getFolderById(folderID);
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    var fileUrl = "";
    var fileName = "";

    
    if (formObject.myFile.length > 0) {
      var blob = formObject.myFile;
      var file = folder.createFile(blob);
      file.setDescription("Uploaded by " + formObject.first_name);
      fileUrl = file.getUrl();
      fileName = file.getName();
    } else{
      fileUrl = "Record saved without a file";
    }

    
    sheet.appendRow([
      formObject.first_name,
      formObject.last_name,
      formObject.gender,
      formObject.dateOfBirth,
      formObject.email,
      formObject.phone,
      fileName,
      fileUrl,
      Utilities.formatDate(new Date(), "GMT+5:30", "yyyy-MM-dd'T'HH:mm:ss'Z'")]);
    
    
    return fileUrl;
    
  } catch (error) {
    return error.toString();
  }
}

I tried to find some references but didn't get any clear explanation on how to do this. I'll take any suggestions anyone can offer.

Solution

  • From your reply, your situation is as follows.

    • The current Web App setting as follow: 1. Execute as: myself (my account) 2. Access to app: only me
    • do you want to avoid editing your Spreadsheet by users while you want to share it with users? - Yes, that's correct

    In this case, your Web Apps can be accessed by only the owner of the Web Apps. And, your Spreadsheet is shared with other users as the editor. I thought that this might not be your expected situation.

    In order to achieve your goal that you want to make users put the values to your Spreadsheet using the HTML form, while your Spreadsheet is not shared with users, please do the following flow.

    1. Please set your Web Apps as one of the following settings. By this setting, the script of Web Apps is run as the owner of Web Apps even when other users access your Web Apps.

      • Execute as: Me and Who has access to the app: Anyone with Google account

        • In this case, the user is required to log in to Google account.

      • Execute as: Me and Who has access to the app: Anyone

        • In this case, the user is not required to log in to Google account.

    2. When you set the above setting to your Web Apps, your Spreadsheet is not required to be shared with the users.

    References: