Why do I have to trigger macro second time after Authorization

[Till Koch]

Hey everyone, 

I have a functioning script that works perfectly fine but there is a usability issue I am trying to resolve. 

The script is part of a sheets template. Hence, everytime someone used the template and triggers the macro, it asks for authorization. 

The issue is, that the user needs to trigger the macro a second time after the authorization. It looks like even that authorization blocks the execution of the script. 

Any suggestions on how to make that smoother? See code below. 

Thanks! 

function EstimateUpload() {
  // get all sheet IDs
  var spreadsheet = SpreadsheetApp.getActive();
  var currentSpreadSheetId = spreadsheet.getId()
  var Datasheet = spreadsheet.getSheetByName("Data Sheet");
  var SpreadsheetOfProposalDatabase = SpreadsheetApp.openById("xxx");
  var SheetOfSpreadsheetOfProposalDatabase = SpreadsheetOfProposalDatabase.getSheets()[0];


  // get data from data sheet
  var cellvalue = "";
  var cell
  var values=[currentSpreadSheetId];
  var i;
  for (i = 4; i <500; i++) {
  cell= "B"+i
  cellvalue = Datasheet.getRange(cell).getDisplayValue()
  values.push(cellvalue)
  }
  Logger.log(values);
  Logger.log(values.length)
 
 
// Appends a new row with 3 columns to the bottom of the
// spreadsheet containing the values in the array
 


var ClientName = Datasheet.getRange("B4");
var ProjectName = Datasheet.getRange("B5");
var docuploadedstatus = Datasheet.getRange("B1262");
 
  if(ClientName.isBlank()||ProjectName.isBlank()){
    Browser.msgBox('Information missing ',"Please make sure to enter Client name as well as Project Name first",Browser.Buttons.OK);


  }else{
   
    if(docuploadedstatus.isBlank()){
   
    // check if row estimate was previously uploaded and if so, return row number
    function rowOfEmployee(){
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var data = SheetOfSpreadsheetOfProposalDatabase.getDataRange().getValues();
   
    for(var i = 0; i<data.length;i++){
    if(data[i][0] == currentSpreadSheetId){ //[1] because column B
      return i+1;
    }
  }
 
}
     var rownumber= rowOfEmployee()
     Logger.log("entry in row number: "+rownumber)
   
    if(rownumber==null){
    SheetOfSpreadsheetOfProposalDatabase.appendRow(values);
     
   
   
}else{
  SheetOfSpreadsheetOfProposalDatabase.deleteRow(rownumber);
  SheetOfSpreadsheetOfProposalDatabase.appendRow(values);


}
   
    // set values in checking column
    var valuestopaste = [ ];
    for(i=0; i <values.length; i++){
    valuestopaste.push([values[i]])}
    var range = Datasheet.getRange("C3:C499");
    Logger.log(valuestopaste);
    range.setValues(valuestopaste);
   
     Datasheet.getRange('B1262').activate();
     Datasheet.getCurrentCell().setValue("uploaded");
     Datasheet.hideSheet();  
     
    fixingrates()
    setEditors()
    Fixingsheet()
   
    }else{    Browser.msgBox('Good news: sheet was already uploaded ',"Sheet is already available for reporting; treat yourself to a coffee :) ",Browser.Buttons.OK);


   
   
    };
};
};


function AddDeliverableAndActivity() {
 
  var spreadsheet = SpreadsheetApp.getActive();
  var currentSpreadSheetId = spreadsheet.getId();
  var SpreadsheetOfDeliverablesAndActivities = SpreadsheetApp.openById("xxx");
  var SheetOfSpreadsheetOfDeliverablesAndActivities = SpreadsheetOfDeliverablesAndActivities.getSheets()[0];
                         
                                           
 
var Datasheet = spreadsheet.getSheetByName("Data Sheet");
var NewActivity = Datasheet.getRange("B152");


SheetOfSpreadsheetOfDeliverablesAndActivities.appendRow([
      "",NewActivity.getDisplayValue(),
    ]);


  var responsemessage= NewActivity.getDisplayValue() + " has been added and can now and in future estimations be selected above"
  Browser.msgBox('Done ',responsemessage ,Browser.Buttons.OK);
 
};

[CBMServices Web]

Can you explain the process a little more? Why are you using it as a template? Can you not just have a permanent installation of it and use WebApps to collect new project info?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/02e9fac9-22c7-4f04-8741-4340db04b809o%40googlegroups.com.

[Till Koch]

Hey George, 

 

Thanks for the response. 

 

Here the process: 

In google sheet 1: a person can complete an estimation and then make it available in sheet 2 for checking some details on it. The connection is made through a script that uploads the sheet 1 ID to a google sheet and then a importrange that imports that information into the 2nd sheet. 

 

Important to note, the estimation in sheet 1 is then further adjusted, but the importrange function in sheet 2 makes sure the information in sheet 2 is always up to date. Hence, it is not just a submission. 

 

Do you think this can be achieved in an easier way? 

 

The current issue triggering the script to upload/sync the estimation, the user needs to authorize the script. After authorizing the user then needs to trigger the macro a second time which is not user-friendly and to some it is not clear. 

Till  

[CBM Services]

After the upload to the master sheet, is the new sheet ever used again? If its sole purpose is to just collect info and upload, perhaps it may be easier to just have a sheet in your master that is used for this.

You can have them fill in the information and just use a simple trigger like onEdit to capture all the data and copy it into the master sheet.

How much info is put in the new sheet? Can you use forms instead to collect the data?

Thanks,
George

---

From: 'Till Koch' via Google Apps Script Community
Sent: ‎2020-‎07-‎03 1:40 AM
To: Google Apps Script Community
Subject: Re: [Apps-Script] Why do I have to trigger macro second time afterAuthorization

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/39d545cc-eaf0-418f-b276-5407404ee6cbo%40googlegroups.com.

[tk...@dminc.com]

Yes, after submitting to the master sheet, there can still be changes happening in the original sheet; those changes need to reflect in the master sheet, hence I a using an importrange() function. 

Curious to hear if that is normal that you need to retrigger the macro after authorization.  

[CBM Services]

Yes, also if software changes then you have to reauthorize everything again.

If you must maintain separate spreadsheets for input, then consider adding a button in the template that says "Activate" and that will cause them to do the authorization. This does not eliminate it but making it part of the entry process may make it more acceptable and less ambiguous.

---

From: 'tk...@dminc.com' via Google Apps Script Community
Sent: ‎2020-‎07-‎06 7:33 AM

To: Google Apps Script Community
Subject: Re: [Apps-Script] Why do I have to trigger macro second timeafterAuthorization

Yes, after submitting to the master sheet, there can still be changes happening in the original sheet; those changes need to reflect in the master sheet, hence I a using an importrange() function. 

Curious to hear if that is normal that you need to retrigger the macro after authorization.  

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2e158440-c52a-4bc1-90ac-71a82134d816n%40googlegroups.com.