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
- 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("参照データファイルが配置されていません!");
}
}
- 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
Post a Comment