google-apps-scriptgoogle-sheetsgoogle-photos

Import in my Google Sheet a google picture from Google Photos with Google Script


I am currently working on a project with multiple features. The main goal is to be able to make requests for work interventions via a form. In this one, different drop-down menus allow to select a site, then a sector, then a zone and finally a room (they are all related to each other and at each change, the plan is updated). He then fills in various information and validates his request. It works perfectly and the request is saved in a Google Sheet.

I have also developed other features like archiving completed requests. All the data are stored in the same sheet file, in different tabs (requests, plans, archiving, ...).

I would like to create a functionality that allows me to print a request in progress. And it is on this one that I have difficulties. Indeed, I would like to create a pdf allowing to visualize all the information of the request which was seized in an inputbox (seizure of the id of the request by the user). I create a temporary shhet in order to create my pdf (which is deleted after). I manage to create the provisional sheet, to delete it and to recover all the information except the plan in question. I think the problem comes from the fact that I am using a link from my Google Photos since I tested with a lambda image url and it works. Does anyone have an idea how I can display my pdf map from my Google photos? (the url starts with "https://lh3.googleusercontent.com/" )

Here is the code I use for the map display part:

  // Récupération de l'ensemble des plans dans tPlansZones
  var feuillePlans = classeur.getSheetByName("Plans");
  var tPlansZones = feuillePlans.getRange("E2:F").getValues();
  for (let i = 0; i <tPlansZones.length; i ++){
 // Si le plan correspond, enregistrement de l'url de celui-ci
    if (tPlansZones[i][0]==zoneAdd){
      urlPlan = tPlansZones[i][1];
      break;
    } else {
      continue;
    }
  }
  // Affichage du plan
  var donneePlan = nouveauSheet.getRange('B21');
  donneePlan.setFormula(`=image("${urlPlan}")`);
  SpreadsheetApp.flush();
  donneePlan.copyTo(donneePlan,{contentsOnly: true});
  SpreadsheetApp.flush();
  var fusionPlan = nouveauSheet.getRange("B21:H45").setHorizontalAlignment("center");
  fusionPlan.merge();

Explanation of the code:

1 - I get in tPlansZones all the names of the zones (tPlansZones[0]) and the url of the plans in question (tPlansZones[1])

2 - if the name of the zone entered in the request corresponds to one of the plans of the for loop, I record in urlPlan the url of this one, otherwise I continue

3 - I display the plan (I want to remind you that I tested the url of a random image from a google search, that I assigned it to tPlansZones[1] of the zone to which the request is assigned and that it works)

I don't share screenshots or my sheet file as this is a fairly large company and I don't want to broadcast its content on the web. But if you wish, I can always create "example" sheets to illustrate a bit more what I'm talking about.

Thanks in advance for your help.


Solution

  • Thanks for your answer Tanaike but after many searches during the night on Google, I realized that my problem was with the url of my photo.

    Indeed, the link of Google photos are temporary and are regenerated every 2 or 3 days apparently. That's why my photo was not displayed correctly. I will close the subject and I refer you to this article which deals with the subject

    link