copyto Google Sheets Script adding date and source data in the next column

Question

I am very new to Javascript and Apps Script. I want to create a function that updates another sheet based on a date in a certain range of the active sheet. I run and no error but it doesn't transfer value from active sheet to sheet named "January", in different target url google sheet

data master post

output 1

output 2

output 3

function myFunction4() {
const spreadsheetIds = [
{ id: "1ShPxDW9qhz4aWgaQ1G9oz7w1yh0-Wfe2VItet95UYks", sheetNames: 
["cab1"] },
{ id: "13Dx3ZOpV7baSTadSApIrVVccN-bHrPlHu240Aux0fo0", sheetNames: 
["cab2"] },
{ id: "14EVlqaP1ilXwopgi0ESvp_IKkSyROSF22WzWAcNAJWc", sheetNames: 
["cab3", "cab4"] }
];

const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const srcSheet = srcSpreadsheet.getSheetByName("January");
if (!srcSheet) return;
const date = new Date();
const ssName = srcSpreadsheet.getName();
const range = srcSheet.getRange("A2:C" + srcSheet.getLastRow());
let values = range.getValues();
if (values.filter(r => r.join("")).length == 0) return;
values = values.map(r => [...r, date, ssName]);
range.clearContent();
for (let i = 0; i < spreadsheetIds.length; i++) {
const dstSpreadsheet = SpreadsheetApp.openById(spreadsheetIds[i].id);
for (let j = 0; j < spreadsheetIds[i].sheetNames.length; j++) {
  const targetSheet = 
dstSpreadsheet.getSheetByName(spreadsheetIds[i].sheetNames[j]);
  if (targetSheet) {
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, values.length, 
  values[0].length).setValues(values);
  }
  }
  }
  }

copyto Google Sheets Script adding date and source data in the next column

sample video

which is desired

I want when I click the send button. can add Date and source in the next column

Date = date when sent

source = the name of the workbook that sent it

    otherSheetName.getRange(1,getJumlahKolom+1).setValue("Date").setFontWeight("bold").setHorizontalAlignment("center");  
    otherSheetName.getRange(1,getJumlahKolom+2).setValue("source").setFontWeight("bold").setHorizontalAlignment("center");

Date & source, the function you want to join to myFunction3()

I have to apologize for my poor English skill. Unfortunately, I cannot understand the relationship between your sample Spreadsheets and your showing script and "sample video" and your expected result. Can I ask you about the detail of them? — Tanaike
– Tanaike, Commented Jan 30, 2023 at 5:13
Thank you for replying. I have to apologize for my poor English skill. In your question, you say but it doesn't transfer value from active sheet to sheet named "January". But, in your added sample, it seems that the values from the "January" sheet of Spreadsheet "A" is copied to "cab1" sheet of Spreadsheet "B". And, in your added script, "January" sheet is not used. As the result, what is your actual expected result? I'm confused. I apologize for this. — Tanaike
– Tanaike, Commented Jan 30, 2023 at 6:28
Date & source, the function you want to join to myFunction3() . Tanaike — dra
– dra, Commented Jan 30, 2023 at 6:57

Tanaike · Accepted Answer · 2023-01-30 07:44:20Z

1

I believe your goal is as follows.

You want to achieve your goal by modifying your showing script.

In this case, how about the following modification?

Modified script:

function myFunction3() {
  const spreadsheetIds = [
    { id: "1aUoRKCnztsZAvbMwSa8Zk-gB-zfn1KeQnJgWIGRVu24", sheetNames: ["cab1"] },
    { id: "1Eme5Rb9_5kbGaT-HTy5ThR1WYUeR1fkQbn2-8wY-uUY", sheetNames: ["cab2"] },
    { id: "150DduDdhFJLC0LL7iOihYa6V1vaZckvtRxJUqCFFV9Q", sheetNames: ["cab3", "cab4"] }
  ];

  const srcSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = srcSpreadsheet.getSheetByName("input");
  if (!srcSheet) return;
  const date = new Date();
  const ssName = srcSpreadsheet.getName();
  const range = srcSheet.getRange("A2:C" + srcSheet.getLastRow());
  let values = range.getValues();
  if (values.filter(r => r.join("")).length == 0) return;
  values = values.map(r => [...r, date, ssName]);
  range.clearContent();
  for (let i = 0; i < spreadsheetIds.length; i++) {
    const dstSpreadsheet = SpreadsheetApp.openById(spreadsheetIds[i].id);
    for (let j = 0; j < spreadsheetIds[i].sheetNames.length; j++) {
      const targetSheet = dstSpreadsheet.getSheetByName(spreadsheetIds[i].sheetNames[j]);
      if (targetSheet) {
        targetSheet.getRange(targetSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
      }
    }
  }
}

When this script is run, the values are retrieved from the source sheet, and the retrieved values are put to the destination sheet by adding 2 columns of date and source.

Note:

Please confirm the source and destination sheet names again. Because it seems that the sheet names of your showing script are different from your sample.

answered Jan 30, 2023 at 7:44

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

dra Over a year ago

source = sheet names , yes

dra Over a year ago

otherSheetName.getRange(1,getJumlahKolom+1).setValue("Date").setFontWeight("bold").setHorizontalAlignment("center");

dra Over a year ago

otherSheetName.getRange(1,getJumlahKolom+2).setValue("source").setFontWeight("bold").setHorizontalAlignment("center");

dra Over a year ago

sorry if I don't understand. if my english is wrong

Tanaike Over a year ago

@dra Thank you for replying. Although I'm not sure whether I could correctly understand your replies, do you want to set the bold and the center text alignment to all rows of the columns "D" and "E"?

|

Collectives™ on Stack Overflow

copyto Google Sheets Script adding date and source data in the next column

1 Answer 1

Modified script:

Note:

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Note:

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related