Donate. I desperately need donations to survive due to my health

Get paid by answering surveys Click here

Click here to donate

Remote/Work from Home jobs

How to share SpreadSheet file with Google App Script

I have some SpreadSheet file what I write some script in. They are a simple input form and data spreadsheet. Now I want to share that file to my employees. But my employees cannot use them like me (trigger not working, script not work exactly). In SpreadSheet I use SpreadSheet, Driver, Session and UserProperties, DocumentProperties. I think have some problem with their folder permission.

How can I share them and make them work like when I use them?

My situation like that:

A../Data - Report data will be save in this folder

B../Report - We will view report by this SpreadSheet

C../DayByDay - We will input and save report data by this SpreadSheet

D../UserData - We will save user data and restaurant info in this SpreadSheet

  1. Problem 1

I want when my employees open Sheet C, depent on data in D username and restaurant name of this user to SpreadSheet.

This function work perfectly with my account but when I shared, my employees open C with other gmail, my user info will be showed. I think problem in var user_email = Session.getActiveUser().getEmail();

//Variables=================
//Properties keys+++++++++++
var parent_folder_key = "parent_folder";
var user_id_key = "user_id";
var user_office_id_key = "user_office_id_key";
var user_office_name_key = "user_office_name";

var data_folder_name = ".....";
var form_sheet_name = "...";
var data_file_name = "...";
var template_file_name = "...";
var template_sheet_name = "...";
var data_total_folder_name = "Data";

var user_info_sheet_name = "担当者情報";
var office_info_sheet_name = "店舗情報";
var thisSpread = SpreadsheetApp.getActiveSpreadsheet();
var dataSpread;

var form_sheet = thisSpread.getSheetByName(form_sheet_name);
var user_info_sheet;
var office_info_sheet;

var ui = SpreadsheetApp.getUi();
var scriptProp = PropertiesService.getScriptProperties();
var userProp = PropertiesService.getUserProperties();
var documentProp = PropertiesService.getDocumentProperties();

//Triggers==================
function onOpen(e) {
    openingLoad();
}

//Methods==================
    //Set all global variables
    var parent_folder = getParentFolder();
    documentProp.setProperty(parent_folder_key, parent_folder.getId());

    var user_email = Session.getActiveUser().getEmail();
    var dataSpreadFile = parent_folder.getFilesByName(data_file_name);
    
    if(dataSpreadFile.hasNext())
    {
        dataSpread = SpreadsheetApp.open(dataSpreadFile.next());

        user_info_sheet = dataSpread.getSheetByName(user_info_sheet_name);
        office_info_sheet = dataSpread.getSheetByName(office_info_sheet_name);
        
        //Get office name and name of user
        var user_info_index = findValueInRange(user_info_sheet, 2, user_email);
      

        if(user_info_index != -1)
        {
            var user_office_id = user_info_sheet.getRange(user_info_index + 2, 4).getValue();
            var user_name = user_info_sheet.getRange(user_info_index + 2, 3).getValue();
            var user_id = user_info_sheet.getRange(user_info_index + 2, 1).getValue();

            userProp.setProperty(user_id_key, user_id);
            userProp.setProperty(user_office_id_key, user_office_id);

            //Set user name
            form_sheet.getRange("C5").setValue(user_id + " - " + user_name);
            
            var user_office_index = findValueInRange(office_info_sheet, 1, user_office_id);
            
            if(user_office_index != -1)
            {
                //Set office name
                var user_office_name = office_info_sheet.getRange(user_office_index + 2, 2).getValue();
                userProp.setProperty(user_office_name_key, user_office_name);
                form_sheet.getRange("C2").setValue(user_office_id + " - " + user_office_name);
            }
        }
    }
    else
    {
        ui.alert("参照データファイルが配置されていません!");
    }
}

  1. Problem 2

In the code above, I save restaurant id and name on var userProp = PropertiesService.getUserProperties(); and I will load this info when I excute the following function. But with my employees, this info is null.

function registerData(){
    var parent_folder = DriveApp.getFolderById(documentProp.getProperty(parent_folder_key));

    var add_date = new Date(form_sheet.getRange("C3").getValue());
    var data_folder = getFolderInFolder(parent_folder, data_total_folder_name);
    var coffee_wagon_data_folder = getFolderInFolder(data_folder, data_folder_name);
    var year_data_folder = getFolderInFolder(coffee_wagon_data_folder, add_date.getFullYear());
    
    var data_file_name = userProp.getProperty(user_office_id_key) + "_" + userProp.getProperty(user_office_name_key);
    
    .....some function..............

}

Comments