data.forEach error

[Admin Blindage]

Hi, hope you are all well.

I have some difficults trying to run this code.

data.forEach is not a function

Can someone help me?

const sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1K9bI86i16uOQXk3E7_dLR4y2VyI9pVzNx_PvtOpEHgg/edit#gid=727713935");

const docFile = DriveApp.getFileById("1qXda20f7p66s-XvuZUGD-O4NveO8lSDz50Xa7Mne3JQ");

const tempFolder = DriveApp.getFolderById("16mO-l5VrWBUDBk5SWd0Iex0a0u4YuY6H");

const pdfFolder = DriveApp.getFolderById("1KQQH1cUm7FImDaXJbs-3DMEqWyTZ4zgR");

const currentSheet = sheet.getSheetByName("Contratos");

const data = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 25).getDisplayValue();

function createBulkPDFss(){

data.forEach((row, index) => {

  createPDF(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20], docFile, tempFolder, pdfFolder, "Contrato" + row[0] + " Apartamento " + row[6], index)

 });

}

function createPDF(arrendatario, nacionalidad, cedula, estadoCivil, celular, ciudad, apto, tipo, duracion, inicio, final, canon, canon2, canonnum, inquilino1, cedinquilino1, inquilino2, cedinquilino2, deposito, garnum, docFile, tempFolder, pdfFolder, pdfName, index) {

const tempFile = docFile.makeCopy(tempFolder);

const tempDocFile = DocumentApp.openById(tempFile.getId());

const body = tempDocFile.getBody()

  

  body.replaceText("{arrendatario}", arrendatario);

  body.replaceText("{nacionalidad}", nacionalidad);

  body.replaceText("{ced}", cedula);

  body.replaceText("{estadocivil}", estadoCivil);

  body.replaceText("{celular}", celular);

  body.replaceText("{ciudad}", ciudad);

  body.replaceText("{apto}", apto);

  body.replaceText("{tipo}", tipo);

  body.replaceText("{duracion}", duracion);

  body.replaceText("{inicio}", inicio);

  body.replaceText("{final}", final);

  body.replaceText("{canon}", canon);

  body.replaceText("{canon2}", canon2);

  body.replaceText("{canonnum}", canonnum);

  body.replaceText("{inquilino1}", inquilino1);

  body.replaceText("{cedinquilino1}", cedinquilino1);

  body.replaceText("{inquilino2}", inquilino2);

  body.replaceText("{cedinquilino2}", cedinquilino2);

  body.replaceText("{deposito}", deposito);

  body.replaceText("{garnum}", garnum);

  tempDocFile.saveAndClose();

  const pdfContentBlob = tempFile.getAs(MimeType.PDF);

  const pdfFile = pdfFolder.createFile(pdfContentBlob).setName(pdfName);

  tempFolder.removeFile(tempFile);

  const pdfUrl = pdfFile.getUrl();

  currentSheet.getRange(index+2, 24, 1, 1).setValue(pdfUrl); 

}

[Tanaike]

About `data.forEach is not a function`, in your script, I thought that `const data = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 25).getDisplayValue();` is `const data = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 25).getDisplayValues();`. How about this?

[Michael O'Shaughnessy]

I agree with Tanaike.  Also, just adding my 2 cents.....

You are passing a LOT of variables!  I suggest just sending the whole array to your function and then grabbing what you need there.  You could use the spread operator to "map" your array to variables if you want. Here is a link to a site that talks about it:https://appdividend.com/2022/07/05/javascript-spread-operator/

Another suggestion would be to turn your array into an object that looks like JSON.  Here is a little script that takes an array and turns it into an object and a function to go the other way also.  Doing this will allow you to the header values with your array.  For example you can use data.arrendatario instead of data[0]. NOTE: to use "makeData" send your array wrapped in curly braces for example: let MyObject = makeData({data})

//takes 2d array and returns key:value as well as the headers

const makeData = ({values})=> {

const [headers,...data] = values

return {

data: data.map(row=>headers.reduce((p,c,i)=>{

p[c] = row[i]

return p

},{})),

headers

}

}

//takes key:value data and returns 2d array

//The passed data has to be an obje IN side and array ie [{id:name}]

const makeValues = ({data}) =>{

// derive the headers from the data

const headers = Object.keys(data.reduce((p,row)=> {

Object.keys(row).forEach(col=>p[col]=col)

return p

},{}))

// combine the headers and the values

return [headers].concat(data.map(row=>headers.map(col=>row[col])))

}

On Tue, Dec 6, 2022 at 7:08 PM Tanaike <kanshi...@gmail.com> wrote:

About `data.forEach is not a function`, in your script, I thought that `const data = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 25).getDisplayValue();` is `const data = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 25).getDisplayValues();`. How about this?

--
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/33730c39-016c-4eae-b12a-01bd54650961n%40googlegroups.com.

[Admin Blindage]

Thanks both for the review and solutions.

I am new to programming. I´m trying to apply the solution, but could not overcome the situation. Also i keep getting another error now. I haven´t made any changes to the code, but it now also says:

7:20:58 Error

TypeError: docFile.makeCopy is not a function

createPDF @ Codez.gs:17

(anónimo) @ Codez.gs:11

createBulkPDFss @ Codez.gs:10

Appreciate if maybe you could correct the code for me? 

CODE:

const sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1K9bI86i16uOQXk3E7_dLR4y2VyI9pVzNx_PvtOpEHgg/edit#gid=727713935");

const docFile = DriveApp.getFileById("1qXda20f7p66s-XvuZUGD-O4NveO8lSDz50Xa7Mne3JQ");

const tempFolder = DriveApp.getFolderById("16mO-l5VrWBUDBk5SWd0Iex0a0u4YuY6H");

const pdfFolder = DriveApp.getFolderById("1KQQH1cUm7FImDaXJbs-3DMEqWyTZ4zgR");

const currentSheet = sheet.getSheetByName("Contratos");

const data = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 25).getValues();

function createBulkPDFss(){

data.forEach((row, index) => {

  createPDF(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20], row[21], docFile, tempFolder, pdfFolder, "Contrato" + row[0] + " Apartamento " + row[6], index)});

 

}

function createPDF(arrendatario, nacionalidad, cedula, estadoCivil, celular, email, ciudad, apto, tipo, duracion, inicio, final, canon, canon2, canonnum, inquilino1, cedinquilino1, inquilino2, cedinquilino2, deposito, garnum, docFile, tempFolder, pdfFolder, pdfName, index) {

const tempFile = docFile.makeCopy(tempFolder);

const tempDocFile = DocumentApp.openById(tempFile.getId());

const body = tempDocFile.getBody()

  

  body.replaceText("{arrendatario}", arrendatario);

  body.replaceText("{nacionalidad}", nacionalidad);

  body.replaceText("{ced}", cedula);

  body.replaceText("{estadocivil}", estadoCivil);

  body.replaceText("{celular}", celular);

  body.replaceText("{email}", email);

  body.replaceText("{ciudad}", ciudad);

  body.replaceText("{apto}", apto);

  body.replaceText("{tipo}", tipo);

  body.replaceText("{duracion}", duracion);

  body.replaceText("{inicio}", inicio);

  body.replaceText("{final}", final);

  body.replaceText("{canon}", canon);

  body.replaceText("{canon2}", canon2);

  body.replaceText("{canonnum}", canonnum);

  body.replaceText("{inquilino1}", inquilino1);

  body.replaceText("{cedinquilino1}", cedinquilino1);

  body.replaceText("{inquilino2}", inquilino2);

  body.replaceText("{cedinquilino2}", cedinquilino2);

  body.replaceText("{deposito}", deposito);

  body.replaceText("{garnum}", garnum);

  tempDocFile.saveAndClose();

  const pdfContentBlob = tempFile.getAs(MimeType.PDF);

  const pdfFile = pdfFolder.createFile(pdfContentBlob).setName(pdfName);

  tempFolder.removeFile(tempFile);

  const pdfUrl = pdfFile.getUrl();

  currentSheet.getRange(index+2, 29, 1, 1).setValue(pdfUrl); 

}

[cbmserv...@gmail.com]

This is indicating that docFile is not a file object. Most likely it was not able to get the file from the id you entered. Check the id you entered to ensure it is a valid file id.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/cd4ee69b-72e3-4e4f-8fc9-eaebadd0ae18n%40googlegroups.com.

[Fredrik]

Hello, I'm trying to populate my sheet with search results of value in column A (website in this case) in column B (LinkedIn profile/company URL starting from column B and expanding to 10 columns to the right).

I came across Giacomo Melzi's code, have pasted it below. It almost does the job, works fine when returning a single URL

Could use your help to turn this code into something that returns both company / profile URLs from custom search result set to search only in 'linkedin.com' (Google's Programmable Search Engine)
First 10 links in the search populated across columns for each search keyword in first column.


--------------



/* MIT License
Copyright (c) 2022 Giacomo Melzi
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE. */

/**
 * Find the url of a Linkedin profile
 *
 * @param {string} query The person or company whose profile you want to find on Linkedin
 * @param {bool} company [OPTIONAL] Type TRUE if you're looking for a company profile. Default is FALSE
 * @param {number} index [OPTIONAL] Get a different result index. Default is 1. Write 0 to return all the results
 * @return if found, the hyperlink of the profile
 * @customfunction
 */
function LINKEDINPROFILE(query, company, index = 1) {

  const apiKey = "yourApiKey";
  const searchEngineId = "yourSearchEngineId";

  const urlQuery =
    `https://www.googleapis.com/customsearch/v1?key={{apiKey}}&cx={{searchEngineId}}&q={{query}}&num=10`;

  const url = urlQuery
    .replace("{{apiKey}}", encodeURIComponent(apiKey))
    .replace("{{searchEngineId}}", encodeURIComponent(searchEngineId))
    .replace("{{query}}", encodeURIComponent(query));

  const response = UrlFetchApp.fetch(url);

  try {
    const res = JSON.parse(response.getContentText());

    let results = res.items.map(e => {
      return e.formattedUrl;
    });

    let output;

    if (company) {
      output = results.filter(e => {
        return e.includes("/company/");
      });
    } else {
      output = results.filter(function(e, i) {
        return e.includes("/in/");
      });
    }

    output = getResults(output,index);

    return output;
  }
  catch (err) {
    throw new Error (err)
  }
}

const getResults = (data,index) => {
  let res;

  if (index == 0) {
    res = data.map((e,i) => `${i+1} ${e}`);
    res.flat();
  } else {
    res = data[index-1];
  }
  console.log(res);
  return res;
}
Footer



Any help is much appreciated, thanks!


BR | JFP

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/006001d90c03%247b2a3860%24717ea920%24%40gmail.com.

--

Regards

Fredrik Parker

(+91) 852-590-5978

/fredrikparker

[cbmserv...@gmail.com]

Fredrik,

 

You should start a new conversation for this issue rather than replying to an existing one. Someone familiar with LinkedIn interface should be able to help here. But also provide your own code on how you are saving the results to spreadsheet.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFL6K9mu%3DDPvd9fdx8pxWmfnApR1mOjq0hi1UeT_XA87bFJd0w%40mail.gmail.com.

[Fredrik]

Also this code runs everytime the sheet is opened, thus exhausting the daily 100 free API calls per day limit in the process.
If we could only run by the click of a button, it'd be really cool!

https://stackoverflow.com/questions/58885035/how-to-fix-the-service-invoked-too-many-times-for-one-day-urlfetch-error

[Michael O'Shaughnessy]

Well, you ran into an issue of getting your parameters messed up.  When you send parameters to a function it does a "1 to 1" matching.  In other words, the first variable sent matches the first parameter variable.  So for your code

row[0] matches function parameter arrendatario AND row[1] matches parameter nacionalidad and so on.  If you look close you are sending 27 variables BUT your function is using 26.  So you are sending variable row[21] and it is matching the function variable of docFile. 

And row[21] I would imagine is NOT a file......

So, what to do here....

First DON"T send all the "row[0], row[1]..."  just send the whole "row" .  Next, put all the other variables you want into a nice object.  Right after "const data =..." do this:

let info = {}

info.docFile = docFile

info.tempFolder = tempFolder

info.pdfFolder = pdfFolder

info.currentSheet = currentSheet

Then your function call on line 11 will now look like this:  createPDF(row,info)

Then change your function parameters to something like this: createdPDF(theData, theInfo){....}

Then in the function change this line:

const tempFile = docFile.makeCopy(tempFolder);  

to:

const tempFile = theInfo.docFile.makeCopy(theInfo.tempFolder);

and then all your replace texts you need to change to look like this:

  body.replaceText("{arrendatario}", theData[0]);

  body.replaceText("{nacionalidad}", theData[1]);

Then at the end you need to update the code to match the following:

  const pdfFile = theInfo.pdfFolder.createFile(pdfContentBlob).setName(pdfName);

  tempFolder.removeFile(tempFile);

  const pdfUrl = pdfFile.getUrl();

  theInfo.currentSheet.getRange(index+2, 29, 1, 1).setValue(pdfUrl); 

I am not sure if I caught all the updates BUT hopefully you see sending two objects to your function is A LOT better than sending them 1 by 1.....

Have a look at this site to learn more about objects.  

And keep at it!!  In coding the journey is as important as the destination!!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/006001d90c03%247b2a3860%24717ea920%24%40gmail.com.