CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-apitriggersgoogle-sheets-api
Stephen Schonewolf
Stephen Schonewolf

Reputation: 9

Google Scripts onEdit showing API Call Error

I have a system to collect time from job tickets that my employees fill out and have that information collected daily in a common timesheet. For each job, we are copying a template and filling it with new information. I need the automatic trigger because some jobs have 10 entries, while some run for much longer and may have 75-100 entries. What I am trying to create is an automatic function to copy info from the job ticket to a place easily accessed by the common timesheet script which runs on a daily trigger.

It is incredibly unwieldy for each user to authorize every job ticket to have it update their time. Also, from a user point of view, they just won't do it. I have tried to set up an onEdit function so it will automatically run without their need to approve, but when I do it I get "GoogleJsonResponseException: API call to sheets.spreadsheets.values.get failed with error: The request is missing a valid API key."

Note: I can cause the function to run manually with no issues from the Script Editor. It is only when it tries to run on its own that I have issues. I also have enabled the SheetsAPI on the Advanced Google Services

Here's my code for the onEdit from change in a particular range:

function onEdit(e) {
  if (
    e.source.getSheetName() == "Form" &&
    e.range.columnStart == 0 &&
    e.range.columnEnd == 7 &&
    e.range.rowStart >= 26 &&
    e.range.rowEnd <= 5000
  ) {
    GetHours();
  }
}

And for the updating:

function GetHours() {
  var ss = SpreadsheetApp.getActive();
  var ssid = SpreadsheetApp.getActive().getId();
  var formssheet = ss.getSheetByName("Form");
  var scriptsheet = ss.getSheetByName("Script Work")
  var workrange = ss.getRange("Script Work!A23:E")
  var tactics = Sheets.Spreadsheets.Values.get(ssid, "Form!A27:H");
  var ticketno = Sheets.Spreadsheets.Values.get(ssid, "Form!B1").values;
  var jobname = Sheets.Spreadsheets.Values.get(ssid, "Form!D1").values;
  var array = []

  var currentDate = new Date();
  var pastweek = new Date();
  pastweek.setDate(currentDate.getDate() -8);

  workrange.clear()
     
     for(var i = 0; i < tactics.values.length; i++){
       var worker = tactics.values[i][0];        
       var hours = tactics.values[i][2];
       var date = tactics.values[i][4];
       var desc = tactics.values[i][6];
    
          if(worker !== ''){
            if(new Date(date) > pastweek){
              array.push([ticketno,jobname,worker,hours,date,desc]);
            }
          }
          else{
            break
            }  
        }
        
   if(array[0]){
    scriptsheet.getRange(scriptsheet.getLastRow()+1,1,array.length,6).setValues(array);
    }
}

Upvotes: 0

Views: 168

Answers (1)

NightEye
NightEye

Reputation: 11184

Issue:

They cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization, but a simple trigger can translate a phrase with the Language service, which is anonymous.

Solution 1:

Use Installable Triggers instead.

Installable triggers, however, offer more flexibility than simple triggers: they can call services that require authorization, they offer several additional types of events including time-driven (clock) triggers, and they can be controlled programmatically.

Simple vs Installable:

sample

Solution 2:

Another way is you could change how your script gets the values. You can use:

formssheet.getRange("A27:H").getValues()

instead of

Sheets.Spreadsheets.Values.get(ssid, "Form!A27:H");

I don't really know what you're thinking by using Sheets API from services but if there is a reason to it, then just change it to Installable Trigger as stated above then it should work.

Upvotes: 1

Related Questions